Forum Discussion

Muhannadtaghi's avatar
Muhannadtaghi
Frequent Visitor
6 years ago

DAX - How to Count the Consecutive Occurrences

Hi There; I would like to count the consecutive worked days for a group of employees, I have a table of employee work date, employee ID, employee name as shown below and I wont to identify who worke...
smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
3 years ago

Muhannadtaghi  you would need an rowNum/Index column in the dataset and then you can write a measure like this

prev = 
VAR prevIndex =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = MAX ( 'Table'[Index] ) - 1 )
    )
VAR prevId =
    CALCULATE (
        MAX ( 'Table'[Employee ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = prevIndex )
    )
VAR cond =
    IF ( MAX ( 'Table'[Employee ID] ) <> prevId, MAX ( 'Table'[Index] ) ) 
RETURN
    cond



consecutiveDaysCount = 
VAR currDate =
    MAX ( 'Table'[Date] )
VAR curr =
    MAX ( 'Table'[Index] )
VAR __topN =
    MAXX (
        TOPN (
            1,
            FILTER (
                SUMMARIZE (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Employee Name] = MAX ( 'Table'[Employee Name] )
                            && 'Table'[Index] <= [prev]
                    ),
                    'Table'[Index]
                ),
                [Index] <= curr
            ),
            [Index], DESC
        ),
        [Index]
    )
VAR test =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = __topN )
    ) 
--var debugger = TOCSV(__lastDate, -1, ",")
RETURN
    DATEDIFF ( test, currDate, DAY )

 

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thank you for this, this is already a great explanation. I am trying to do something similar, except the Index column won't work because, say there are multiple people who work on the same day - this is what my data is like. For example:

Date -- Name 

1/1/23 -- John

1/2/23 -- Sam

1/3/23 -- Sam

1/3/23 -- Dave

1/4/23 -- Sam

 

For the bottom record, we would want Sam to show 3 days, but if you use the Index, it wouldn't catch it because of the "Dave" record. Any ideas to amend above to calculate the consecutive days for this case?? Thank you!