Forum Discussion
Rolling 12 months
Hi Ashish_Mathur ,
Please find PBIX attached. What i am trying to do is:
a) create a calculated column that looks at the posting date field and returns rolling 12M for that date for each vendor . i.e. if posting date is 06.06.2019 then the spend value for 06.06.2019 should be a rolling sum of spend between 06.06.2018 till 06.06.2019.
b) based on the value, i want to create another column that tags the fields based on the $ amount. i.e
Here are the upper and lower limits:
| Category | |
| <5000 | Level 0 |
| >=5000 and <25 000 | L1 |
| >= 25 000 and < 50 000 | L2 |
| >= 50 000 and < 100 000 | L3 |
Appreciate any help on this. Thanks!
also noting that need to be able to filter the table based on financial year and business line.
Super UserHi,
Should the rolling spend start from July of every year or from the very inception? Also, your buckets seem incomplete - what about amounts which exceed 100,000?
Hi Ashish_Mathur ,
Should be from July going back 12 months for each FY, summarised by vendor. And if it spills over, it goes into an 'Exceeded' bucket.- Ashish_Mathur
Hi Ashish!
Appreciate your help. Unfortunately the requirement is to be able to use the Levels measure as a slicer value which is where i'm struggling. That was why i was trying to calculate rolling 12m in a calculated column so that i could assign levels via a column and then use taht as a slicer.