cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## Summing distinct count with filters

Hi guys,

I am trying to do a rolling sum of new employee I have. I have an employee table and a date table

Employee table:

 Employee ID Start Date End Date A 2023-01-01 2023-06-30 B 2023-02-03 2023-06-15 C 2023-02-15 2023-07-15 D 2023-03-15 2023-07-15 E 2023-03-20 2023-07-15

Date table:

 DATE 2023-01-01 2023-01-02 2023-01-03 2023-01-04

I have created a measure to dispaly new hire daily:

New hire_daily =

Calculate (

Distinctcount ('employee'[employee ID],

FILTER ('employee', 'employee'[Start date] = MAX('datetable'[date]))))

The measure works as intended as i created a line chart with date axis and it display the new hire as 1 on different date.

I am trying to create another measure to show a rolling sum of the new hire as time goes by. However, none of the methods I tried work. They all display the new hire separately on different days.

For instance, i tried:

New Hire Year to Date = sumx( value(datetable[date]), [New hire_daily])

This did not work.

Thank you

1 ACCEPTED SOLUTION
Community Support

Hi @ljx0648 ,

You can follow the steps below to get it:

1. Please don't create any relationship between Date table and Employee table

2. Create a measure as below to get the rolling count of employees

New hire_daily =
VAR _seldate =
MIN ( Date[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'employee'[employee ID] ),
FILTER (
ALLSELECTED ( Employee ),
Employee[Start Date] < _seldate
&& Employee[End Date] > _seldate
)
)

3. Create a visual as below with the date field of Date table and the above measure

In addition, you can refer the following links to get it.

Total Number Of Staff Over Time - Power BI Insights - YouTube

How Many Staff Do We Currently Have - Multiple Dates Logic In Power BI Using DAX | Enterprise DNA

If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your visual settings. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

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.
5 REPLIES 5
Super User

@ljx0648, Try this measure:
MEASURE = CALCULATE([New hire_daily], FILTER(datetable,datetable[Date]<=MAX(datetable[Date])))

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Helper I

Thank you for your suggestion here.

Unfortunately, this does not work as well, this measure displayed as an overlapping line (same as the [New hire_daily]) in the line chart =(

Super User

@ljx0648 Can you try to add ALL inside FILTER:

MEASURE = CALCULATE([New hire_daily], FILTER(ALL(datetable),datetable[Date]<=MAX(datetable[Date])))

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Helper I

I have added in all as suggested, but nothing changes

Community Support

Hi @ljx0648 ,

You can follow the steps below to get it:

1. Please don't create any relationship between Date table and Employee table

2. Create a measure as below to get the rolling count of employees

New hire_daily =
VAR _seldate =
MIN ( Date[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'employee'[employee ID] ),
FILTER (
ALLSELECTED ( Employee ),
Employee[Start Date] < _seldate
&& Employee[End Date] > _seldate
)
)

3. Create a visual as below with the date field of Date table and the above measure

In addition, you can refer the following links to get it.

Total Number Of Staff Over Time - Power BI Insights - YouTube

How Many Staff Do We Currently Have - Multiple Dates Logic In Power BI Using DAX | Enterprise DNA

If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your visual settings. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors