Forum Discussion

kaledjeff's avatar
kaledjeff
Icon for Advocate I rankAdvocate I
8 years ago
Solved

Number of employee between two dates

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 ...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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..

     

  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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')
        )
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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..

 

  • kaledjeff's avatar
    kaledjeff
    Icon for Advocate I rankAdvocate I
    8 years ago

    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.

    • Phil_Seamark's avatar
      Phil_Seamark
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      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')
        )
      • kaledjeff's avatar
        kaledjeff
        Icon for Advocate I rankAdvocate I
        8 years ago

        Thank you, Phil_Seamark. It works great.

         

        KeithChu I'll try your formula and let you know.

         

        Thank you both for your contibrution. 

         

        BR

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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
    )
)