Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Distinct count with datediff between column date and selected date value

Hi Team,

 

I am new to Power BI and is currently working on a calculation which I cannot figure out.

I am trying to count distinct number of employees from a fact table using the following calculated measure. 

 

employee_count_90days = calculate(distinctcount(employees[employee#]), FILTER(employees, employee_status = "Active" && DATEDIFF(employees[hire_date],selectedvalue(calendar[Date]),DAY) > 90))

 

What I need is to exclude the weekends from the calculation between the hire_date column and the selected date from the calendar slicer. 

 

Hoping for someone to help. 

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Hope calender date is joined with hire date , in hat you need work day rank and use that for last 90 work day

     

    Work Day = if(WEEKDAY([Date],2)>=6,0,1)
    Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
    Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
    Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)

     

    employee_count_90days = calculate(distinctcount(employees[employee#]), FILTER(employees, employee_status = "Active" ), filter('calendar',  DATEDIFF('calendar'['Work Date Cont'],selectedvalue(calendar[Work Date Cont]),DAY) > 90))

     

    or refer

    Traveling Across Workdays - What is next/previous Working day
    https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Amitchandak,

       

      Thanks for this however my calendary table is not connected to the fact table.

      Would there be a solution for this scenario?

       

      I appreciate your big help!

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    Hi Anonymous ,

     

    According to your description, two additional filters need to be added to exclude weekend dates. Refer to a similar test.

    employee_count_90days =
CALCULATE (
    DISTINCTCOUNT ( employees[employee#] ),
    FILTER (
        employees,
        DATEDIFF ( employees[hire_date], SELECTEDVALUE ( calendar[Date] ), DAY ) > 90
            && employees[Col_week] <> 7
            && employees[Col_week] <> 6
    )
)
    Col_weekday = WEEKDAY('employees'[hire_date])

     

    If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.


    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi v-henryk-mstf 

       

      Thanks for the answer.

       

      What we need is date difference between employee hire date and selected calendar date but excludes the weekend from the calculation between the 2 dates. Something like below and the calendar table is not connected to fact table.

       

      employee_count_90days = calculate(distinctcount(employees[employee#]), FILTER(employees, employee_status = "Active" && DATEDIFF(employees[hire_date],selectedvalue(calendar[Date]),DAY) - (**weekends between the hire date column and slicer selected date ) > 90))

      • v-henryk-mstf's avatar
        v-henryk-mstf
        Community Support
        4 years ago

        Hi Anonymous ,

         

        It is not yet clear, is it possible to provide test data and screenshots of the expected results?


        Best Regards,
        Henry