Forum Discussion
VinLee1314
3 years agoNew Member
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
- tamerj1Community Champion
Hi VinLee1314
Please try
Cumulative Leavers =
SUMX (
FILTER (
ALLSELECTED ( 'Calendar'[Year] ),
'Calendar'[Year] <= MAX ( 'Calendar'[Year] )
),
[Leavers]
)- VinLee1314New Member