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 worked 2 or more days in a row.

I need your help to suggest a DAX that count the consecutive occurrence only, for example, in my table below the employee Jones worked 2 days in a row in the 3rd and 4th days of the month, this should be one occurrence and he worked 2 days in a row in the 12th and 13th days of the month, that should be another separate occurrence.

All that being said, I need a DAX for counting the consecutive occurrences separately for each employee but not accumulatively.

Please feel free to contact me with any questions/ clarifications.

Many thanks, đź™‚

 

 
DateEmployee IDEmployee NameConsecutive Days
1/1/20200011Sam 
1/2/20200012Jim 
1/3/20200013Jones 
1/4/20200013Jones2
1/5/20200011Sam 
1/6/20200014Joseph 
1/7/20200011Sam 
1/8/20200011Sam2
1/9/20200011Sam3
1/10/20200012Jim 
1/11/20200014Joseph 
1/12/20200013Jones 
1/13/20200013Jones2

13 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Here is one approach to do this.  It does a calculation to find the last date the employee did not work, so I needed to create a Date table called "WorkDates" that has all dates with WorkDates = CALENDAR(MIN(Work[Date]), MAX(Work[Date])).  But you can adapt this for any Date table.

     

    Number Consecutive Days =
    VAR selecteddate =
    SELECTEDVALUE ( 'WorkDates'[Date] )
    VAR lastnotworkeddate =
    CALCULATE (
    MAXX (
    FILTER ( WorkDates, ISBLANK ( CALCULATE ( COUNTROWS ( 'Work' ) ) ) ),
    WorkDates[Date]
    ),
    WorkDates[Date] < selecteddate
    )
    VAR diff =
    DATEDIFF ( lastnotworkeddate, selecteddate, DAY )
    RETURN
    IF ( COUNTROWS ( 'Work' ) > 0, IF ( diff > 1, diff ) )

     

     

    If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • Muhannadtaghi's avatar
      Muhannadtaghi
      Frequent Visitor
      6 years ago

      Hi Mahoneypat;

      Thanks a lot for your support. Unfortunately, it did not work, it was showing blank.

      Below are all the steps that I did to test it out:

      • I renamed the same example table that I shared with you to “Work” to align with the date table and DAX
      • Created the “WorkDates” table
      • Applied the DAX exactly as you post it, please see below.

      Please advise if I missed something?

      Many thanks,

       

      WorkDates tableConsecutive Days DAX

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        Did you make a 1:Many relationship between WorkDates and Work tables?

         

        Regards,

        Pat

         

    • Meagan_Lea's avatar
      Meagan_Lea
      New Member
      3 years ago

      Hi mahoneypat , I am trying to use your measure above and it is not working. I am wondering if it is due to the last statement? 

      IF ( COUNTROWS ( 'Work' ) > 0, IF ( diff > 1, diff ) )

      Is the true statement missing from the first part and the false statement missing from the second statement?

      IF ( COUNTROWS ( 'Work' ) > 0 [True missing], IF ( diff > 1, diff ), [False missing] )

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        3 years ago

        With the IF function, if you don't provide a false option, it returns blank. The expression is complete, accepting the default BLANK() for both falses.

        Pat

  • 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!