rolling 12 months

Question

Hi All,&nbsp;Im looking at doing a rolling 12 months total.I have a list of employee id's and I have done a count of these then used the calculate to give me the max date, however when I put this into a matrix table&nbsp; it gives me the count of the number of employee id's but what I really need is to show that if I am reporting for June 23, it should then show me the total number of employees we had going from June 23 back 12 months, May 23 going back 12 months etc... but its not.&nbsp;Employee ID&nbsp; &nbsp; &nbsp; &nbsp;Left Date&nbsp; &nbsp;&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 16/08/20222&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;17/08/20183&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/09/20224&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;02/05/20235&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;03/11/20226&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15/05/20237&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;27/04/20158&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;16/06/2020&nbsp;I used the following calculation:&nbsp;Last 12 months rolling =&nbsp; &nbsp; CALCULATE(SUM(Headcount[Employee Id]),&nbsp; &nbsp; &nbsp; &nbsp; DATESINPERIOD(Leavers[Left Date],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MAX(Leavers[Left Date]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -12,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MONTH&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp;Any help would be amazing.&nbsp;Thanks in Advance!

greg_deckler · Answer

UK_User123456&nbsp;See if this helps, just have to change from AVERAGE to SUM:

&nbsp;

uk_user123456 · Answer

Greg_Deckler&nbsp;Many thanks for your response, but it didnt really resolve the issue.&nbsp;&nbsp;I dont know if its possible, but if I use a bar graph as an example, and had 12 months worth of data bars, I would then look at Jul 23, that bar for Jul 23 should be a sum of the last 12 months in the 1 bar, then in the bar for May 23, it would be the sum of the last 12 months in that one bar etc.... so for each bar in the graph would be the sum up of that month plus the prior 12 months from that point in the month.&nbsp;Hope that makes sense.

Forum Discussion

rolling 12 months

2 Replies

Related content

12-month rolling cumulative

Rolling 12 Month calculation

12 month rolling turnover

Rolling 12-Month Employee Turnover

DAX rolling 12 months and last year