Solved

Sum of last 12 months before the switchover month

  • 2 August 2023
  • 3 replies
  • 345 views
C
Rank
Userlevel 2
Badge +5

Hello, i would like to do the sum or the average of last 12 months before the switchover month. 

Thanks for your help

icon

Best answer by Gawain 22 September 2023, 15:30

View original

3 replies

Mat
Rank
Userlevel 3
Badge +4

Hi @Cedric LJ ,

 

I think this question has already been asked here: 


@Khalid Awale gave a great explanation that I think should cover exactly what you need! Let us know if you have any questions!

 

-Mat

Mat | Pigment Specialist
C
Rank
Userlevel 2
Badge +5

Hi @Mat , I have checked the thread but it only mentions YTD , MTD or QTR metrics or maybe I have missed the point.
My request it to create a sum of a metrics based on the last 12 months before the switch over date.
For example if the switchover date is July 23 i want to sum all the months between July 22 and June 23.

Gawain
Rank
Userlevel 4
Badge +5

Hello,

One way to solve this would be to identify with a boolean the 12 Month before the switchover data. This can be done with this formula:
If(MONTHDIF(Month.'End Date','Switchover date')<=12

AND Month.'End Date'< 'Switchover date',true)

You can then use this expression to filter your initial Metric and only keep the data on the last 12 Month. By finally removing your Month dimension you will have the sum you want.

The full formula could like this:
‘Your Metric’[filter: If(MONTHDIF(Month.'End Date','Switchover date')<=12

AND Month.'End Date'< 'Switchover date',true)]

[REMOVE SUM: Month]

 

If your ‘Switchover date’ Metric is by Version, don’t forget to do [ADD CONSTANT: Version] to the Month.’End Date’  in the MONTHDIF function.

 

Hope it helps!

Reply


Ask your question here!

Terms & ConditionsCookie settings