cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ffernan New Member
New Member

Getting Rolling Sum From another DAX Measure

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.

2.PNG

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

3.PNG

 

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

4.PNG

 

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

 

5.PNG

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Getting Rolling Sum From another DAX Measure

Hi @ffernan ,

 

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.
1-1.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Getting Rolling Sum From another DAX Measure

Hi @ffernan ,

 

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.
1-1.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,935)