Summing distinct count with filters

[ljx0648]

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.

Please help, any tips are appreciated! 

Thank you

[Tahreem24]

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

[ljx0648]

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

[Tahreem24]

@ljx0648 Can you try to add ALL inside FILTER:

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

[ljx0648]

I have added in all as suggested, but nothing changes 

[Anonymous · 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

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