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('Calendar'[Date], 'HR Dataset'[Term date])
    ) + 0
 
Cumulative Mthly Leavers =
CALCULATE
(
    [Leavers],
    FILTER
    (
        ALLSELECTED ( 'Calendar'[Month] ),
        'Calendar'[Month] <= MAX ( 'Calendar'[Month] )
    )
)

 

Thanks!

Vin

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' )
    )

4 Replies

  • daXtreme's avatar
    daXtreme
    Solution Sage
    3 years ago
    // # 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
  • 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
      Community 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' )
      )