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 calcula...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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!