Cumulative measures count

[VinLee1314]

Hi,

I'm trying to get my "cumulative Leavers" column working but to no avail. "Cumulative Leavers" simply calculate a running total of the "Leavers" column. Please help!

My measures are as follows:

New Hire =

    CALCULATE(

        [Count of Employees],

        USERELATIONSHIP('Calendar'[Date], 'HR Dataset'[Hire date])

    )

Cumulative New Hire =

CALCULATE(

    [New Hire],

    FILTER(

        ALL('HR Dataset'),

        'HR Dataset'[Hire date] <= MAX('Calendar'[Date])

    )

)

Leavers =

CALCULATE(

    [Count of Employees],

    USERELATIONSHIP('Calendar'[Date], 'HR Dataset'[Term date])

    ) + 0

Cumulative Leavers =

CALCULATE(

    [Leavers],

    USERELATIONSHIP('Calendar'[Date], 'HR Dataset'[Term date]),

    FILTER(

        ALL('HR Dataset'),

        'HR Dataset'[Term date] <=  MAX('Calendar'[Date])        

        )

)

My model is as follows:

I suspect it's relationship-related but counldn't pin point where the issue is. Your help will be much appreciated!

Vin

[tamerj1 · Accepted solution]

Hi @VinLee1314 

Please try 

Cumulative Leavers =
SUMX (
FILTER (
ALLSELECTED ( 'Calendar'[Year] ),
'Calendar'[Year] <= MAX ( 'Calendar'[Year] )
),
[Leavers]
)

[VinLee1314]

Hi @tamerj1,

Excellent! Thanks for your help!