Consecutive Absent/Worked Days Power BI Measure

Question

I am current trying to Dynamically Calculate Consecutive worked and absent days for a group of employees, however this as proven to be quite a difficult task. The RAW table looks something like this:

Date	ID	Absent
6/1/2021	1234	1
6/2/2021	1234	1
6/32021	1234	1
6/4/2021	1234	0
6/1/2021	6789	1
6/2/2021	6789	0

I would like to use Dax (not Power Query) to calculate the consecutive days they were absent, the closest I have come to a solution is the following, Shout out SQLBI:

DaysWith0 = 
VAR CurrentDate = MAX('Calendar'[Date])
VAR FirstDateEver =  CALCULATE(MIN( 'Calendar'[Date]), REMOVEFILTERS())
VAR PrevWorked =
    CALCULATE(
        MAX('RawDataTable'[Date]),'Calendar'[Date] <= CurrentDate) 
VAR PrevDate = COALESCE(PrevWorked,FirstDateEver)
VAR Result = INT(CurrentDate - PrevDate)  
RETURN
    Result

But this only counts the consecutive days that have no data (no schedule). I need 2 measures that shows the consecutive worked days, and the consecutive absents.

John Cappelletti · Answer 1 · 2021-06-22T19:37:41.567

0

This looks like a classic Gaps-and-Islands

Example

Select DateR1 = min(Date)
      ,DateR2 = max(Date)
      ,ID
      ,Absent = sum( case when Absent=1 then 1 else 0 end )
      ,Worked = sum( case when Absent=0 then 1 else 0 end )
 From  (
        Select *
              ,Grp = datediff(day,'1900-01-01',Date) - row_number() over (partition by ID,Absent order by Date) 
         From YourTable
       ) A
 Group By ID,Grp
 Order By ID,min(Date)

Results

DateR1      DateR2      ID      Absent  Worked
2021-06-01  2021-06-03  1234    3       0
2021-06-04  2021-06-04  1234    0       1
2021-06-01  2021-06-01  6789    1       0
2021-06-02  2021-06-02  6789    0       1

edited Jun 22 '21 at 19:37

answered Jun 22 '21 at 18:49

John Cappelletti

79,615
7
44
66

Hey John, Thanks for your response, however i think im a bit lost. The table i have has over 20k rows and counting so i couldn't declare the table . What im looking of its a Measure that i can just drag to my existing visual. Apologies for being such a noob :(. I think your response is tailored more to SQL but that is currently a last resort as i have other cases i will need to apply this to. – Puno Jun 22 '21 at 19:30
@Puno The (at)YourTable is just a demonstrative table variable ... See updated answer. I removed the "noise" – John Cappelletti Jun 22 '21 at 19:37

Consecutive Absent/Worked Days Power BI Measure

1 Answers1