Forum Discussion
Rolling 12 months
Hi All,
i need to calculate rolling 12 months from received date and make it a calculated column. Reason is because i need to create groupings based on the cost range these values fall under.
i.e. if a value is between 10k and 50k, it gets tagged as 'L1', if it's between 50k and 100k, it gets tagged as 'L2' etc.
This will then be used for a slicer.
This is what i'm using but i'm not getting right results. I have no data before Jul2018 but somehow it's returning 1816. For each month, it should giving me rolling 12 months spend. Anyone know what's happening? thank you in advance!
11 Replies
- AlexisOlson
Super User
What table is the calculated column on?
Hi AlexisOlson ,
It's calculated on my Data table (not my calendar table). i suspect that's causing an issue? Essentially i need to get rolling 12 months at each date point in my fact/data table but as a calculated column so that i can use that column to categorise my fields into dollar buckets.
- Ashish_Mathur
Hi,
Share the link from where i can download your PBI file. Also, what are the grouping bucket - show the lower and upper limit of each bucket.
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.
- Ashish_Mathur
Hi,
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?
