Forum Discussion

VinLee1314's avatar
VinLee1314
New Member
3 years ago
Solved

Cumulative monthly count

Hi all,   Couldn't get my "Cumulative Mthly Leavers" column to work. Would appreciate your guidance, please.     Leavers = CALCULATE(     [Count of Employees],     USERELATIONSHIP('...
cumulative
running total
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi VinLee1314 

    you have to YearMonth number column in your date table. The try the following 

    Cumulative Mthly Leavers =
    CALCULATE (
    [Leavers],
    'Calendar'[YearMonth] <= MAX ( 'Calendar'[YearMonth] ),
    ALLSELECTED ( 'Calendar' )
    )

VinLee1314's avatar
VinLee1314
New Member
3 years ago

I managed to get it working on a monthly basis. I would like the running total to continue after the year ends. How do I make changes to the DAX?

 

Cumulative Leavers =
CALCULATE
(
    [Leavers],
    FILTER
    (
        CALCULATETABLE
        (
            SUMMARIZE('Calendar', 'Calendar'[Date].[MonthNo], 'Calendar'[Date].[Month]),
            ALLSELECTED('Calendar')
        ),
        ISONORAFTER
        (
            'Calendar'[Date].[MonthNo], MAX('Calendar'[Date].[MonthNo]), DESC,
            'Calendar'[Date].[Month], MAX('Calendar'[Date].[Month]), DESC
        )
    )
)
 
Thanks!
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi VinLee1314 

you have to YearMonth number column in your date table. The try the following 

Cumulative Mthly Leavers =
CALCULATE (
[Leavers],
'Calendar'[YearMonth] <= MAX ( 'Calendar'[YearMonth] ),
ALLSELECTED ( 'Calendar' )
)