Skip to main content

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!

 

Hi, ​@valef 

CUMULATE() with the filter works as expected.


But then by adding nBY: 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


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? 


Hey ​@valef ,

In this case, you don’t need to use CUMULATE.
You could do the following

'Nexus Lowest Level IS'lBY SUM:MONTH.YEAR]YSELECT:YEAR."FY 26"]

Here is a detailed explanation of this formula :

  • ‘Nexus Lowest Level IS’ is you source metric by Month
  • Using the modifier cBY 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.
  • cSELECT:Year.”FY 26”] filter the FY 26 Year as well as removing the Year dimension. You will have.

Hope this helps,


Thank you! It works. 

 

Appreciate your help! 


Reply