Forum Discussion

icos's avatar
icos
Frequent Visitor
3 years ago
Solved

Headcount 12 Rolling Month

Hi all,

I've been trying to do this calculation and also see on previous posts, but couldn't find a solution that fits my needs.

I have already successfuly calculated the Headcount by month, but I am having an hard time trying to do the 12 rolling month calculation.

I am sending a pbix file with the same structure as my real data and also with the Headcount per month measure already calculated.

Would really appreciate your help on this!! Thank you 🙏

  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Thank you for your message, and please check the below suits your requirement.

    The file is also attached.

     

    Expected result measure: = 
SUMX (
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'Date' ),
            'Date'[Date] > EOMONTH ( MAX ( 'Date'[Date] ), -12 )
                && 'Date'[Date] <= EOMONTH ( MAX ( 'Date'[Date] ), 0 )
        ),
        'Date'[MonthYearId], 'Date'[Year], 'Date'[Month], 'Date'[Month (#)]
    ),
    [# Employees]
)

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

    Expected result measure: = 
SUMX (
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'Date' ),
            'Date'[Date] > EOMONTH ( MAX ( 'Date'[Date] ), -12 )
                && 'Date'[Date] <= EOMONTH ( MAX ( 'Date'[Date] ), 0 )
        ),
        'Date'[MonthYearId]
    ),
    [# Employees]
)

     

    • icos's avatar
      icos
      Frequent Visitor
      3 years ago

      Thank you for your answer! It is indeed the result that I was expecting, but could you please let me know if instead of the "MonthYearID" column I can show the results grouped my "Month (Year)" (text column). When I try to do the change, it does not retrieve accurate results

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        3 years ago

        Hi,

        Thank you for your message, and please check the below suits your requirement.

        The file is also attached.

         

        Expected result measure: = 
SUMX (
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'Date' ),
            'Date'[Date] > EOMONTH ( MAX ( 'Date'[Date] ), -12 )
                && 'Date'[Date] <= EOMONTH ( MAX ( 'Date'[Date] ), 0 )
        ),
        'Date'[MonthYearId], 'Date'[Year], 'Date'[Month], 'Date'[Month (#)]
    ),
    [# Employees]
)