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 IDHire dateIdentity
101439292

1-Jul-

23

IFS
10142511118-Jun-23Tech Org
10142187018-Jun-23Tech Org
10142283018-Jun-23Tech Org
10142527718-Jun-23Tech Org

 

 

 

Target:

MonthIdentityCumulative
JuneIFS0
JuneTech Org4
JulyTech Org4
JulyIFS1
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

1 Reply

  • 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