POWER QUERY/POWER BI - For loop over dates for group of users

Question

I have a problem I don't know how to tackle : I need to return a value, either 0, 1, or 2 to identify the number of REASON "Absent" :

For a unique User :

If the REASON is not "Absent", return 0
If the REASON is "Absent" for less than 3 consecutive days, return 1
If the REASON is "Absent" for more than 3 consecutive days, return 2
Next user

Do I need to use GroupBy and then create a loop over dates? How do I manage consecutive dates?

Regards,

Example

Agustin Palacios Agustin Palacios · Accepted Answer · 2020-12-03T03:29:03

Use the following dax formula to create the column:

Value = 
VAR __user = 'Table'[Username]
VAR __date = 'Table'[Date]

VAR __3previousDayTable =
    FILTER( 
        'Table', 
        'Table'[Username] = __user &&
        'Table'[Date] >= __date - 2 &&
        'Table'[Date] <= __date  &&
        'Table'[Reason] <> BLANK()   
    )
VAR __valueCount = COUNTROWS( __3previousDayTable )
Return 
    IF( 
        'Table'[Reason] = BLANK(), 
        0, 
        IF( 
            __valueCount < 3, 
            1,
            2
        )
    )

This is the expected result:

POWER QUERY/POWER BI - For loop over dates for group of users

1 Answers