Starters/leavers - cumulative

[redhughes]

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!

[themistoklis]

@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]

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)

[v-shex-msft · Accepted solution]

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