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...
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

     

    • Muhannadtaghi's avatar
      Muhannadtaghi
      Frequent Visitor
      6 years ago

      Hi mahoneypat; 

      Thanks for your reply, I tried all 4 different types of cardinalities and all types of cross filter directions, but still showing blank.

      Also, I used the DAX in measure and calculation column, but nothing changed.

      Please advise.

      Many thanks, 

       

      RelationshipBlank

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

        My bad for not clarifying I was using the measure in a table with WorkDates[Date] and Work[EmployeeID].  To use it in a card, use this measure that references the first one.

         

        Total Consecutive Days =
        SUMX (
        SUMMARIZE ( 'Work', WorkDates[Date], 'Work'[Employee ID] ),
        [Number Consecutive Days]
        )

         

        And the relationship needs to be changed back too.  1:Many from WorkDates to Work (Filter going 1:many too).

         

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

        Regards,

        Pat