Home Forums Power BI calculating the last 12 months

This topic contains 1 reply, has 2 voices, and was last updated by  mgrisham 4 months, 2 weeks ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #9487

    Rich_Coutinho
    Participant
    • Started: 9
    • Replies: 16
    • Total: 25

    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.
    #10118

    mgrisham
    Participant
    • Started: 1
    • Replies: 6
    • Total: 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/

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.