Hello, i would like to do the sum or the average of last 12 months before the switchover month.
Thanks for your help
Solved
Sum of last 12 months before the switchover month
- Master Author
Best answer by Gawain
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!
+6- Wallflower
Hi
I think this question has already been asked here:
-Mat
Mat | Pigment Specialist
- Master Author
Hi
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.
+9- Employee
- Answer
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!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.