Cumulative monthly count

[VinLee1314]

Hi all,

Couldn't get my "Cumulative Mthly Leavers" column to work. Would appreciate your guidance, please.

Leavers =

CALCULATE(

    [Count of Employees],

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

    ) + 0

Cumulative Mthly Leavers =

CALCULATE

(

    [Leavers],

    FILTER

    (

        ALLSELECTED ( 'Calendar'[Month] ),

        'Calendar'[Month] <= MAX ( 'Calendar'[Month] )

    )

)

Thanks!

Vin

[VinLee1314]

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 · Accepted solution]

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]

Hi @tamerj1 ,@daXtreme ,

Thank you for your help!

[daXtreme]

// # Leavers TD = count of leavers to date
[# Leavers TD] = 
var LastVisibleDate = MAX( 'Calendar'[Date] )
var Output = 
    CALCULATE(
        [# Leavers],
        'Calendar'[Date] <= LastVisibleDate,
        ALLSELECTED ( 'Calendar' )
    )
return
    Output