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 tried many measures to calculate but not getting the result. 

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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