Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Cumulative count by month by type

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 ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Anonymous ,

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