Hi everyone,
I'm trying to use the cumulate function to sum only the FY26 months of the metric Nexus Lowest Level, but the output includes all years instead of just FY26.
Has anyone run into this or knows how to fix it?
Thanks in advance!
Solved
Cumulate function
- Apprentice Author
Best answer by CDALMAY
Hey
In this case, you don’t need to use CUMULATE.
You could do the following
'Nexus Lowest Level IS'[BY SUM:MONTH.YEAR][SELECT:YEAR."FY 26"]Here is a detailed explanation of this formula :
- ‘Nexus Lowest Level IS’ is you source metric by Month
- Using the modifier [BY SUM:Month.Year] you are aggregating all the Month using the Year property, so you lose month and have now a metric by Year. Note : SUM is optional here as the default aggregation method is SUM.
- [SELECT:Year.”FY 26”] filter the FY 26 Year as well as removing the Year dimension. You will have.
Hope this helps,
+4- Trendsetter
Hi,
CUMULATE() with the filter works as expected.
But then by adding [BY: Month.Year] modifier you actually replace Month with Year - you can see that the total for FY24 is repeated for each month.
Please pay attention to the orange indicator on the left.
If you open the metric with the BY modifier in Formula Playgroud and chose automatic structure, you will see that this metric have Year dimension, not Month.
Hope it helps.
Yuri
- Apprentice Author
Hi Yuri! Thanks for your response!
I did not quite understand what’s the final solution tho. My formula is dimensionalized by year already. What should I do to just sum FY26 and not all years in the cumulate formula?
+14- Master Helper
- Answer
Hey
In this case, you don’t need to use CUMULATE.
You could do the following
'Nexus Lowest Level IS'[BY SUM:MONTH.YEAR][SELECT:YEAR."FY 26"]Here is a detailed explanation of this formula :
- ‘Nexus Lowest Level IS’ is you source metric by Month
- Using the modifier [BY SUM:Month.Year] you are aggregating all the Month using the Year property, so you lose month and have now a metric by Year. Note : SUM is optional here as the default aggregation method is SUM.
- [SELECT:Year.”FY 26”] filter the FY 26 Year as well as removing the Year dimension. You will have.
Hope this helps,
Clement
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.