Skip to content

Fix Power BI "Number of Months" parameter causing incorrect date range calculation#1852

Merged
flanakin merged 3 commits into
devfrom
copilot/fix-power-bi-cost-report
Jan 8, 2026
Merged

Fix Power BI "Number of Months" parameter causing incorrect date range calculation#1852
flanakin merged 3 commits into
devfrom
copilot/fix-power-bi-cost-report

Conversation

Copilot AI commented Oct 10, 2025 •
edited
Loading

Copy link
Copy Markdown
Contributor

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:

  1. Opening a Power BI report with the fix applied
  2. Setting "Number of Months" to 3
  3. Verifying the START HERE page shows the correct date range (e.g., "Jul 1, 2024 - *" when run in October 2024)
  4. 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 #1839

💬 Share your feedback on Copilot coding agent for the chance to win a $200 gift card! Click here to start the survey.

@microsoft-github-policy-service microsoft-github-policy-service Bot added the Skill: DevOps GitHub setup and automation label Oct 10, 2025
@microsoft-github-policy-service microsoft-github-policy-service Bot added Skill: Documentation Documentation updates Tool: FinOps guide Implementing FinOps guide Needs: Review 👀 PR that is ready to be reviewed labels Oct 10, 2025
@MSBrett
Fix Number of Months date calculation bug
e0f8403 
Co-authored-by: MSBrett <24294904+MSBrett@users.noreply.github.com>
Copilot AI changed the title [WIP] Fix Power BI report excluding first 5 days in March Fix Power BI "Number of Months" parameter causing incorrect date range calculation Oct 10, 2025
Copilot AI requested a review from MSBrett October 10, 2025 20:42
@flanakin flanakin added this to the v13 milestone Jan 6, 2026
@flanakin flanakin marked this pull request as ready for review January 8, 2026 09:29
Copilot AI review requested due to automatic review settings January 8, 2026 09:29
@flanakin flanakin merged commit 9064ae0 into dev Jan 8, 2026
3 checks passed
@flanakin flanakin deleted the copilot/fix-power-bi-cost-report branch January 8, 2026 09:29
Copilot AI reviewed Jan 8, 2026
View reviewed changes

Copilot AI left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Pull request overview

This PR fixes an off-by-one error in the Power BI "Number of Months" parameter calculation that was causing reports to pull data from one extra month than intended. When users set the parameter to 3 (expecting 3 closed months + current month = 4 months of data), the report was incorrectly pulling 5 months of data due to an extra +1 in the date calculation formula.

Key changes:

  • Removed incorrect +1 offset from date calculation formulas in 5 locations across storage-based and KQL-based Power BI datasets
  • Updated changelog to document the fix under Power BI reports v13

Reviewed changes

Copilot reviewed 5 out of 5 changed files in this pull request and generated no comments.

Show a summary per file
File Description
src/power-bi/storage/Shared.Dataset/definition/expressions.tmdl Fixed date calculation in two locations: START HERE display and ftk_Storage function
src/power-bi/storage/Shared.Dataset/definition/tables/StorageData.tmdl Fixed date calculation in StorageData table date filter logic
src/power-bi/kql/Shared.Dataset/definition/expressions.tmdl Fixed date calculation in START HERE display for KQL-based reports
src/power-bi/kql/Shared.Dataset/definition/tables/StorageData.tmdl Fixed date calculation in StorageData table date filter logic for KQL-based reports
docs-mslearn/toolkit/changelog.md Documented the bug fix under Power BI reports v13 with issue reference

flanakin added a commit that referenced this pull request Jan 31, 2026
@MSBrett @flanakin
Fix Power BI "Number of Months" parameter causing incorrect date rang…
d4263f0 
…e calculation (#1852)

Co-authored-by: copilot-swe-agent[bot] <198982749+Copilot@users.noreply.github.com>
Co-authored-by: MSBrett <24294904+MSBrett@users.noreply.github.com>
Co-authored-by: Michael Flanakin <flanakin@users.noreply.github.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

Copilot code review Copilot Copilot left review comments
@flanakin flanakin flanakin approved these changes
@arthurclares arthurclares Awaiting requested review from arthurclares
@aromano2 aromano2 Awaiting requested review from aromano2
@MSBrett MSBrett Awaiting requested review from MSBrett MSBrett is a code owner

Assignees

@flanakin flanakin

@MSBrett MSBrett

@aromano2 aromano2

@arthurclares arthurclares

Copilot code review Copilot

Labels

Needs: Review 👀 PR that is ready to be reviewed Skill: DevOps GitHub setup and automation Skill: Documentation Documentation updates Tool: FinOps guide Implementing FinOps guide

Projects

None yet

Milestone

v13

6 participants

@flanakin @MSBrett @aromano2 @arthurclares