Forum Discussion

kumarrajesh's avatar
kumarrajesh
New Member
3 years ago

Re: how do I post a comment/question

I need help on getting employees who are absent for more than 10 consecutive days Calendar table with Date column UserMst table having employee_name, Punch_Date, Attendance as Present/Absent i...
amitchandak's avatar
amitchandak
Super User
3 years ago

kumarrajesh , With help from date table

 

Create these columns in Date Table joined to date of your table
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)

 

COlumn in Attendance table
Cont =
Var _date = related('Date'[Work Date cont Rank])
var _cnt = calculate(DISTINCTCOUNT(Table[Date]), filter(Table, Table[Emp ID] = earlier([Emp ID])))
return
if(_cnt >10,1, 0)


Now you can create a meausre
calculate(count(Table[Emp ID]), filter(Table, Table[Cont]=1))

 

 

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