cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## 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 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
1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors