Last 12 month mesure problems

Crestous — Wed, 26 May 2021 03:14:20 GMT

Hi guys!

I have here a sample data and a table with the result I would like to get but I cannot

https://drive.google.com/drive/folders/1mM6JwwxCI2HYNOHAkbYw7ZRlkLTjHPVF?usp=sharing

I am trying to create a Rolling measure for the last 12 months, which is a separation ratio I have tried several solutions in he forum:
Solved: Trailing 12 or Rolling 12 month sum - Microsoft Power BI Community
Solved: Rolling 12 month Total - Microsoft Power BI Community

Videos in youtube:
DAX for Power BI - Last 12 Months from Selected Date (Trailing 12 Months TTM) - YouTube
Rolling 12 Months DAX Measure in Power BI - YouTube

and external forums:
dax - How to get rolling 12 month sum in PowerBI - Stack Overflow
Calculate Rolling Totals Using DATESBETWEEN In Power BI | Enterprise DNA
How to get rolling 12 month sum in PowerBI - Intellipaat

This one works as an extra column:

Rolling Separations by year =

VAR START_DATE = TurnOver_Temp[Aux2]

VAR MAX_DATE = TurnOver_Temp[Date Value]

return

CALCULATE(

SUM(TurnOver_Temp[Separations]),

FILTER(ALLEXCEPT(TurnOver_Temp,TurnOver_Temp[Global Wage Class],TurnOver_Temp[Leaving Category]),

TurnOver_Temp[Date Value] >= START_DATE &&

TurnOver_Temp[Date Value] <= MAX_DATE)

)

I can manage to get the sum for the last 12 months properly with a measure. so far for now with extra columns I manage to get the results but in that way my filters are not applying correctly is it possible to get the assistance of anyone?

Re: Last 12 month mesure problems

amitchandak — Tue, 25 May 2021 14:17:18 GMT

Crestous , Usually last 12 or rolling 12 measure should be done with date table.

example measure

Rolling 12 = CALCULATE(Sum(TurnOver_Temp[TurnOver_Temp[Separations],DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Last 12 month mesure problems

Crestous — Wed, 26 May 2021 03:13:40 GMT

HEre you may find the sample data and the result I should get if I group by month, but ideally it should be able to be filtered by any category in the sample data::

https://drive.google.com/drive/folders/1mM6JwwxCI2HYNOHAkbYw7ZRlkLTjHPVF?usp=sharing

topic Re: Last 12 month mesure problems in DAX Commands and Tips

Last 12 month mesure problems

Re: Last 12 month mesure problems

Re: Last 12 month mesure problems