Cumulative count by month by type

[sabry_303]

Hey gurus,

I am attemting to create a measure to calculate the cumulative headcount by month. Would you please help?

My data:

Employee ID	Hire date	Identity
101439292	1-Jul- 23	IFS
101425111	18-Jun-23	Tech Org
101421870	18-Jun-23	Tech Org
101422830	18-Jun-23	Tech Org
101425277	18-Jun-23	Tech Org

Target:

Month	Identity	Cumulative
June	IFS	0
June	Tech Org	4
July	Tech Org	4
July	IFS	1

[v-yiruan-msft · Accepted solution]

Hi @sabry_303 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a dimension table

Identities = VALUES('Table'[Identity])

2. Create a measure as below 

Cumulative = 
VAR _ide =
    SELECTEDVALUE ( 'Identities'[Identity] )
VAR _date =
    SELECTEDVALUE ( 'Table'[Hire date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Employee ID] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Identity] = _ide
                && 'Table'[Hire date] <= _date
        )
    ) + 0

3. Create a visual as below screenshot

Best Regards