Rolling 12 months

[Anonymous]

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!

Rolling = CALCULATE (

SUM ( Data[Spend Amount] ),

ALL ( 'Data' ),

DATESBETWEEN (

'DimDate'[Date],

DATEADD ( LASTDATE ( DimDate[Date] ), -12, MONTH ),

LASTDATE ( 'DimDate'[Date])

)

)

[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.

[Anonymous]

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? 

[Anonymous]

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,

Is this the result you are expecting?  You may download my PBI file from here.

Hope this helps.

[Anonymous]

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. 

[Ashish_Mathur · Accepted solution]

Hi,

Download the revised PBI file from here.

[Anonymous]

Saved my day! Thank you so much this is exactly how i needed it to work! Appreciate it very much

[Ashish_Mathur]

You are welcome.

[AlexisOlson]

What table is the calculated column on?

[Anonymous]

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.