Forum Discussion
Running Total breaks when new year starts
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.
Solution SageHi,
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 MonthNo continuously)
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 MonthNo will not reset at the start of the year, so the running total will simply continue calculating