microsoft
Fix Power BI "Number of Months" parameter causing incorrect date range calculation
Problem
When users set the "Number of Months" parameter to 3 in Power BI Cost Analysis reports, the report was pulling data from 5 months instead of the expected 4 months (3 closed months + current month). This made it appear that the first 5 days of certain months were missing from the analysis.
Root Cause
The date calculation formula had an off-by-one error with an extra +1 in the month offset:
Before (buggy):
Date.AddMonths(startDate, -(#"Number of Months"+1))
After (fixed):
Date.AddMonths(startDate, -#"Number of Months")
Example
When running the report in October 2024 with Number of Months = 3:
- Buggy behavior: Start date = June 1st → Data from June, July, August, September + October (5 months)
- Fixed behavior: Start date = July 1st → Data from July, August, September + October (4 months) ✓
Solution
Removed the incorrect +1 offset from all date calculation formulas across 5 locations in the Power BI datasets:
- Storage-based reports (expressions.tmdl and StorageData.tmdl) - 3 instances
- KQL-based reports (expressions.tmdl and StorageData.tmdl) - 2 instances
This fix aligns the code with the official documentation:
"Number of Months defines how many closed months (before the current month) get shown in reports. The current month is an extra month in addition to the closed number of months."
Testing
This is a pure mathematical correction with no logic changes. Manual validation can be performed by:
- Opening a Power BI report with the fix applied
- Setting "Number of Months" to 3
- Verifying the START HERE page shows the correct date range (e.g., "Jul 1, 2024 - *" when run in October 2024)
- Confirming data includes exactly 4 months (3 closed months + current month)
Files Changed
-
src/power-bi/storage/Shared.Dataset/definition/expressions.tmdl(2 changes) -
src/power-bi/storage/Shared.Dataset/definition/tables/StorageData.tmdl(1 change) -
src/power-bi/kql/Shared.Dataset/definition/expressions.tmdl(1 change) -
src/power-bi/kql/Shared.Dataset/definition/tables/StorageData.tmdl(1 change) -
docs-mslearn/toolkit/changelog.md(documented the fix)
Fixes #1833
Original prompt
This section details on the original issue you should resolve
<issue_title>Power BI Cost Analysis Report Excludes First 5 Days When Month Number = 3</issue_title> <issue_description>## 🐛 Problem I am using FinOps Hub version 012 with the Data Lake configuration enabled. When I configure the Power BI Cost Analysis report and set the month number to 3, the report excludes the first 5 days of the month from the analysis. Please see the attached screenshot for reference.
issue.pdf </issue_description>
<agent_instructions>Triage the issue, research using mslearn, provide proposed solutions along with links/citations to authorative sources.</agent_instructions>
Comments on the Issue (you are @copilot in this section)
Fixes microsoft/finops-toolkit#1839
💬 Share your feedback on Copilot coding agent for the chance to win a $200 gift card! Click here to start the survey.
