Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello DAX Experts,
I'm trying to figure out a formula that calculate the number of employee for each day between two dates. My table looks something like this:
I have a calendar table and I need know for each day how many people we have in IT, HR, Fianance, ...etc.
I very much appreacite your help.
Solved! Go to Solution.
HI @kaledjeff
This calculated table uses an embedded date table
Table = VAR ExpandedTable = GENERATE( CALENDAR(DATE(2017,1,1),TODAY()), FILTER( 'Employees', [Date]>='Employees'[Start Date] && [Date]< IF(ISBLANK('Employees'[End Date]),TODAY(),'Employees'[End Date]) ) ) RETURN SUMMARIZE( ExpandedTable, [Date] , "Count",COUNTROWS('Employees') )
and when plotted you get this..
Hi @kaledjeff
Well done on getting it to work with your date table
If you add [Department] to the final SUMMARIZE statement, you can then use this field as a filter
RETURN SUMMARIZE( ExpandedTable,
[Date] ,
[Department] , "Count",COUNTROWS('Employees') )
I suppose you can add a calculate column on this table.
= COUNTROWS(FILTER('Employee'), [JoinDate] >= [Start Date] && [JoinDate] <= [End Date] && [Employee] = [Employee] && [Department] = [Department])
HI @kaledjeff
This calculated table uses an embedded date table
Table = VAR ExpandedTable = GENERATE( CALENDAR(DATE(2017,1,1),TODAY()), FILTER( 'Employees', [Date]>='Employees'[Start Date] && [Date]< IF(ISBLANK('Employees'[End Date]),TODAY(),'Employees'[End Date]) ) ) RETURN SUMMARIZE( ExpandedTable, [Date] , "Count",COUNTROWS('Employees') )
and when plotted you get this..
Hi,
By expanding it can quickly become a large table if the dataset (like mine) consist of approx 50.000 rows each year for 5+ years.
Is it possible to get the same output using a measure?
Appreciate thoughts around this approach.
Hi,
I believe that I have the solution for how to create this as a measure. See code below
Count =
VAR _EndOfMonth =
SELECTEDVALUE( DateTable[EndOfMonth] ) /*Assuming a custom table with a column "EndOfMonth" has been created*/
RETURN
CALCULATE(
DISTINCTCOUNT( Employees[Employee] ),
Filter(
Employees,
Employees[Start Date] <= _EndOfMonth
&& Employees[End Date] >= _EndOfMonth //Assuming no blanks in End Date
)
)
Hello @Phil_Seamark,
Thank you for your quick reply.
I tried your method but unfornatluty I didn't get the same reults.
I used your method on the calendar table that I have and it worked great except I cannot filter by a department. I understand that I can create a function for department but It'll take a lot to calculate since I have many deprtment. Is there a simpler way?
Thanks.
Hi @kaledjeff
Well done on getting it to work with your date table
If you add [Department] to the final SUMMARIZE statement, you can then use this field as a filter
RETURN SUMMARIZE( ExpandedTable,
[Date] ,
[Department] , "Count",COUNTROWS('Employees') )
I have been trying to figure this out FOREVER. Thank you so much!
Thank you, @Phil_Seamark. It works great.
@KeithChu I'll try your formula and let you know.
Thank you both for your contibrution.
BR
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
84 | |
73 | |
49 |
User | Count |
---|---|
142 | |
133 | |
110 | |
68 | |
55 |