Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
ljx0648
Helper I
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 IDStart DateEnd Date
A2023-01-012023-06-30
B2023-02-032023-06-15
C2023-02-152023-07-15
D2023-03-152023-07-15
E2023-03-202023-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.

 

Please help, any tips are appreciated! 

 

Thank you

 

1 ACCEPTED SOLUTION

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

vyiruanmsft_1-1700548706757.png

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.

View solution in original post

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

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 =(

@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

I have added in all as suggested, but nothing changes 

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

vyiruanmsft_1-1700548706757.png

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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