Forum Discussion
Running Total breaks when new year starts
Good afternoon,
I am looking for some advice with my PowerBi visual shown below. I have browsed similar issues on Forums and made minor changes but I have had no luck! Any help would be greatly appreciated.
Issue: The dotted line is the total planned running from Jan 2025 to Jan 2026 however, PowerBi restarts the count when it gets to Jan 2026.
Requirement: Dotted line does not restart and carries on calculating.
Dotted line formula:
Hello EthanLansera
Audit Plan Running Total =
CALCULATE(
COUNTA('Audit Plan 2023 - 2025 (3 Year Plan)'[Audit Status]),
FILTER(
ALL('Audit Plan 2023 - 2025 (3 Year Plan)'),
'Audit Plan 2023 - 2025 (3 Year Plan)'[Due by Month] <= MAX('Audit Plan 2023 - 2025 (3 Year Plan)'[Due by Month])
)
)It uses the ALL() function instead of ALLSELECTED(), ensuring that the calculation considers all dates in the dataset, regardless of filters or selections. By applying FILTER(ALL(Table), [Date] <= MAX([Date])), it accumulates the count continuously across all years rather than resetting at the start of a new year. This allows the running total to persist seamlessly from one year to the next without being affected by slicers or date context changes.
If that works, please select as answer!
Thank you.
2 Replies
- anilelmastasi
Super User
Hello EthanLansera
Audit Plan Running Total =
CALCULATE(
COUNTA('Audit Plan 2023 - 2025 (3 Year Plan)'[Audit Status]),
FILTER(
ALL('Audit Plan 2023 - 2025 (3 Year Plan)'),
'Audit Plan 2023 - 2025 (3 Year Plan)'[Due by Month] <= MAX('Audit Plan 2023 - 2025 (3 Year Plan)'[Due by Month])
)
)It uses the ALL() function instead of ALLSELECTED(), ensuring that the calculation considers all dates in the dataset, regardless of filters or selections. By applying FILTER(ALL(Table), [Date] <= MAX([Date])), it accumulates the count continuously across all years rather than resetting at the start of a new year. This allows the running total to persist seamlessly from one year to the next without being affected by slicers or date context changes.
If that works, please select as answer!
Thank you.
- Kaviraj11
Solution Sage
Hi,
Use a continuous running total approach: Instead of limiting the logic to a specific month's number, you can calculate the running total based on a unique identifier for each period (e.g., a combination of Year and Month, or just use
MonthNocontinuously)Audit Plan Running Total =
CALCULATE(
COUNTA('Audit Plan 2023 - 2025 (3 Year Plan)'[Audit Status]),
FILTER(
CALCULATETABLE(
SUMMARIZE(
'Audit Plan 2023 - 2025 (3 Year Plan)',
'Audit Plan 2023 - 2025 (3 Year Plan)'[Due By Month].[MonthNo]
),
ALLSELECTED('Audit Plan 2023 - 2025 (3 Year Plan)')
),
'Audit Plan 2023 - 2025 (3 Year Plan)'[Due By Month].[MonthNo] <= MAX('Audit Plan 2023 - 2025 (3 Year Plan)'[Due By Month].[MonthNo])
)
)MONTHNO as a continuous measure: Instead of considering the month as a standalone entity, we're now focusing on the continuous MonthNo. The
MonthNowill not reset at the start of the year, so the running total will simply continue calculating
