Forum Discussion

redhughes's avatar
redhughes
Icon for Helper II rankHelper II
7 years ago
Solved

Starters/leavers - cumulative

Hi,

 

My data set looks similar to this:

 

 

 

 

 

 

This means I can quite easily show starters and leavers per month (both in terms of headcount and hours), and therefore net change. However, I am now thinking of a way to show a cumulative over time, i.e.:

  • on 1/1/18 it'd be 1 (or 40)
  • on 2/1/18 it'd show 2 (77.5)
  • on 3/1/18 it'd show 3 (117.5) and finally
  • on 4/1/18 it'd shows 2 (77.5) again

Any tips on how to model it? What formulas/tables/columns should I be creating? Thanks for all suggestions!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi redhughes,

     

    You can try to use following measure, I added new conditions on leave date to prevent dax formula calculated on leave employees.

    Measure =
CALCULATE (
    SUM ( 'Table'[Hours] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Start Date] <= MAX ( 'Table'[Start Date] )
            && OR ( [Leave date] = BLANK (), [Leave date] >= MAX ( Table[Start Date] ) )
    )
)

     

    Regards,

    Xiaoxin Sheng

3 Replies

  • themistoklis's avatar
    themistoklis
    Icon for Community Champion rankCommunity Champion
    7 years ago

    redhughes

     

    You can use a formula like the following one:

    Cumulative = 
CALCULATE (
    SUM ( 'Table'[Hours] ),
    FILTER( ALL ('Table' ),
    'Table'[Start Date] <= MAX( 'Table'[Start Date] ))
)

    See also workspace on this link

    • redhughes's avatar
      redhughes
      Icon for Helper II rankHelper II
      7 years ago

      thanks themistoklis, unfortunately this measure doesn't take into account leavers so it keeps adding hours on and on disregarding the fact that someone might have left (and would therefore reduce the number of cumulative hours)

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi redhughes,

         

        You can try to use following measure, I added new conditions on leave date to prevent dax formula calculated on leave employees.

        Measure =
CALCULATE (
    SUM ( 'Table'[Hours] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Start Date] <= MAX ( 'Table'[Start Date] )
            && OR ( [Leave date] = BLANK (), [Leave date] >= MAX ( Table[Start Date] ) )
    )
)

         

        Regards,

        Xiaoxin Sheng