Distinct count with datediff between column date and selected date value

[Newbie_sretatap]

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. 

[v-henryk-mstf]

Hi @Newbie_sretatap ,

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.

[Newbie_sretatap]

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]

Hi @Newbie_sretatap ,

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

Best Regards,
Henry

[amitchandak]

@Newbie_sretatap , 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-Calenda...

[Newbie_sretatap]

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!