Forum Discussion

VinLee1314's avatar
VinLee1314
New Member
3 years ago
Solved

Cumulative measures count

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

 

 

 

  • Hi VinLee1314 

    Please try 

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

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion

    Hi VinLee1314 

    Please try 

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