Creating Trailing Twelve Month Sum with a measure - Turnover Rate

[munna825]

I am struggling to create a TTM chart that displays the sum of the last 12 months turnover rate. Calculating the turnover rate in itself was confusing. Now that I have the turnover rate I would like to calculate the trailing twelve months.

Below is the export of what the turnover rate looks at any given time in the month. Now to calculate the ttm of 2020-04 it would be the sum of all the rates between 2019-05 to 2020-04 which equals 93.29%. How can I create a measure that will help me calculate this and represent this in a TTM Chart?

Note: Turnover Rate is also a measure. 

Year-Month	Turnover Rate
2019-01	5.31%
2019-02	10.62%
2019-03	8.93%
2019-04	6.67%
2019-05	14.40%
2019-06	7.75%
2019-07	17.52%
2019-08	11.51%
2019-09	7.14%
2019-10	2.90%
2019-11	5.71%
2019-12	4.32%
2020-01	4.38%
2020-02	5.71%
2020-03	5.84%
2020-04	6.11%

[v-jayw-msft · Accepted solution]

Hi @munna825 ,

You could check the measure below.

Measure 2 = SUMX(FILTER(ALL('Table'),'Table'[Year-Month]<=SELECTEDVALUE('Table'[Year-Month])&&'Table'[Year-Month]>EDATE(SELECTEDVALUE('Table'[Year-Month]),-12)),[Measure])

And the result in table visual will be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@munna825 , try with a date table

Rolling 12 = CALCULATE([Turnover Rate]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Table[Date]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/