I am trying to display a Running Total (RT) that remains accurate across fiscal years, even when a user filters the view to a specific Fiscal Year (FY).
I have a model-level measure calculating the Running Total using the ITERATE function:
1- ITERATE(PRIOR() + [Measure], [Measure], [d/DIM_DATE])
2-- IF(NOT(ISNULL([d/DIM_IMPACT_DATE].[p/FISCAL_CALPERIOD])),LOOKUP([VALUE] ,[d/DIM_DATE] =LastPeriods("Month",1000000 )), IF(NOT(ISNULL([d/DIM_DATE].[p/FISCAL_CALQUARTER])),LOOKUP([VALUE] ,[d/DIM_DATE] =LastPeriods("Quarter",10000 )), IF(NOT(ISNULL([d/DIM_DATE].[p/FISCAL_YEAR])),LOOKUP([VALUE] ,[d/DIM_DATE] =LastPeriods("Year",100 ))))))
Current Configuration & Attempts made:
Restricted Measure: I created a Restricted Measure in the Story using a Calculation Input Control for the Fiscal Year.
Constant Selection: I enabled Constant Selection on the DIM_IMPACT_DATE dimension within the Restricted Measure to try and preserve historical context.
Model-Level Logic: I have tried using LOOKUP and LastPeriods formulas to fetch historical data, but these return nulls (slant lines) or incorrect values once the FY restriction is applied.
Observation: Test measures show that the ITERATE logic works perfectly when all years are visible in the table. The "restart" only happens when the query context is limited to a specific year.
When the table is unfiltered, the values are correct (e.g., FY 2026 correctly includes the cumulative sum of 2024 and 2025). However, as soon as I apply a restriction to show only the selected year, the Running Total restarts from zero for that year.
Note: No standard filter for date only dynamic selection based on FY user is selecting
The Issue:
Even with Constant Selection enabled, the engine seems to be performing a "Pre-Aggregation" filter that strips historical data before the ITERATE function executes, forcing the calculation to restart at the beginning of the filtered period.
Questions:
How can I force the ITERATE function to respect historical data when a Story-level restriction is active?
Is there a specific "Calculation Priority" setting or a "Visual-only Filter" method that allows me to hide previous years without removing their data from the RT calculation context?
Are there any known limitations with ITERATE when combined with Restricted Measures and Constant Selection in the Optimized Story Experience?
I am adding screenshots of an eg how the Running Total created on model level works fine until it is restricted to any year.
Bluesky
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.