cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

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

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 Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors