Getting Rolling Sum From another DAX Measure

[Anonymous]

I want to get a 12-month rolling sum of my headcount. So far I have managed to get the headcount per month, but I need to get the 12-month rolling sum of the headcount.

I have two tables in my Power BI Report:

- I have a table called Headcount which has a list of months.

- An Employee Table which has a list of Employees, their Hire Date and Termination Date

I've created a Headcount measure which calculates the headcount for a specific month, based on their hire date and termination date.

I tried to create another measure which then SUMs the 12 months rolling headcount, but I can't seem to get the right result.

For example, in January 2020 I would like the new column to have 155 because that is the sum of the Headcount between February 2019 and January 2020.

I've included the PBIX file here. Thanks in advance!

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could create the following two measures:

LastYearTest =
SUMX (
    FILTER (
        ALLSELECTED ( Headcount[StartOfMonth] ),
        DATEDIFF (
            Headcount[StartOfMonth],
            SELECTEDVALUE ( Headcount[StartOfMonth] ),
            MONTH
        ) = 12
    ),
    [_Headcount]
)

12MonthSum =
SUMX (
    FILTER (
        ALLSELECTED ( Headcount[StartOfMonth] ),
        Headcount[StartOfMonth] <= MAX ( Headcount[StartOfMonth] )
    ),
    [_Headcount] - [LastYearTest]
)

Here is the result.