calculating the last 12 months

[Rich_CoutinhoParticipant Started: 10Replies: 16Total: 26]

I have a excel file with Payroll number, Hiring date, Leaving date and Etc that i updated monthly… I have created in Power BI a calendar and some measures to calculate the headcount, the leavers, new joiners…

Now I need to calculate the attraction Turnover with this formula:

the leavers in the last 12 months / ((headcount of the month in previous year + headcount of month in current year)/2)

Putting simply:

1. for each month i need to have the last 12 months leavers :  MAR 2018 should be (MAR 2017 until FEB 2018), FEB 2018 should be ( FEB 2017 until JAN 2018), JAN 2018 (JAN 2017 until DEC 2017), DEC 2017 (DEC 2016 until NOV 2017) and so on …
2. For each month should be calculated the sum of headcount for the last and current year:  MAR 2018 and MAR 2017, FEB 2018 and FEB 2017, JAN 2018 and JAN 2017, DEC 2017 and DEC 2016 and so on…..

example 1 calculation for MAR 2018 :

leavers in the last 12 months (MAR 2017 until FEB 2018) = 15

Headcount MAR 2017 =  474,

Headcount for March 2018 =  506

the calculation should be : 15/((474 + 506)/2) = 3.06 %

Example 2 calculation for DEC 2017:

Leavers in the last 12 months from (DEC 2016 until NOV 2017)= 10

headcount DEC 2016 =479

headcount for  2017 = 508

so the calculation should be  10/((479+508)/2) =  2.02%

 

Does somebody have any idea how can i calculate it in powerBI?

Attachments:

You must be logged in to view attached files.

[mgrishamParticipant Started: 1Replies: 6Total: 7]

this post has always helped me when working with date functions, should be simple enough to combine with your date table:

https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/

[Author]

Posts