12 Month Rolling Attrition Graph Issue

[cyborgandy]

Trying to show the attrition for the last 12 month rolling on bar chart in power BI.

Sum of attrition for the last 12 months / average HC for the last 12 months.

The X axis for this graph should be dynamic basis the month selection in the slicer Example if I chose Apr'22 then the X axis should be from May'21 to Apr'22 with each month displaying the attrition % for the last 12 months i.e. May'21 to show attrition from June'20 to May'21, June'21 to show attrition from 'July 20 to June 21

[v-xiaotang]

Hi @cyborgandy 

Thanks for reaching out to us.

>>in graph its not calculating the average headcount of all the 12 months rolling, its only taking the headcount of that month and same goes leaver count

could you share some sample data? thanks.

Best Regards,

Community Support Team _Tang

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

[cyborgandy]

@amitchandak thanks for your reply, still my issue is not resolved ,in graph its not calculating the average headcount of all the 12 months rolling, its only taking the headcount of that month and same goes leaver count, I wanted to show the average leaver%= avarage headcount/total leaver for 12 month rolling as per month selected in slicer

[amitchandak]

@cyborgandy , If you choose one month and what show more than that data on axis, ou need independent date table in slicer

example measure

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI