Forum Discussion

siumui's avatar
siumui
Icon for Helper I rankHelper I
2 years ago
Solved

DAX Count consecutive days

  Hi,   I’m new to Dax.   For each ID I would like to add 1 column name Count.  This would count consecutive days and reset to 1 if not consecutive.  My data do not have weekends date.  So if th...
  • ThxAlot's avatar
    ThxAlot
    2 years ago

    Add a calendar table and an index column to facilitate the calculation; but it's never for a novice as it leverages embedded table iteration.

     

    (2023/11/23 is marked as non-working day on purpose; therefore 2023/11/24 is calculated as consecutive day)

ThxAlot's avatar
ThxAlot
Icon for Super User rankSuper User
2 years ago

Add a calendar table and an index column to facilitate the calculation; but it's never for a novice as it leverages embedded table iteration.

 

(2023/11/23 is marked as non-working day on purpose; therefore 2023/11/24 is calculated as consecutive day)

  • siumui's avatar
    siumui
    Icon for Helper I rankHelper I
    2 years ago

    Hi ThxAlot,

     

    Thank you very much for your help!  I download your file but I'm able to open it.  It says "...is incompatible with your current version of Microsoft Power BI Desktop.  Please install the latest version and try opening the document again."

    If you don't mind can you please post the codes for creating the calendar table?  

    • ThxAlot's avatar
      ThxAlot
      Icon for Super User rankSuper User
      2 years ago

      PQ code for tables: _CALENDAR, ATTENDANCE

      // _CALENDAR
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdErDoAwFETRvVST0Jm2fCQCFIqParr/bYAjJNce8fJuptbg6NRLvUIXruNeQ+s+NGEizC9uy37+taAOdGAknAhnQkVU4QMyM5YpoxZUTBO2aeInZmRjn3k1nM2JD2OeeTkPzBhoXM84X/ry2gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Working Day" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Working Day", type logical}})
in
    #"Changed Type"

// ATTENDANCE
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc67DcAgDAbhXVwjwW8jh8yC2H+NFCgv6dqvuZvTpKqs3jysWM+0VTYegN4IReiEnXAQnoBBdcWNkh7sgO/8F0XohOOH6wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"id", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

       

      DAX for calculated column

      Consecutive = 
VAR __dt = ATTENDANCE[date]
RETURN
    COUNTROWS(
        FILTER(
            ATTENDANCE,
            VAR __dt_ = ATTENDANCE[date]
            RETURN
                ATTENDANCE[id] = EARLIER( ATTENDANCE[id] )
                    && ATTENDANCE[date] <= __dt
                    && EARLIER( ATTENDANCE[Index] ) - ATTENDANCE[index] + 1
                        = COUNTROWS(
                            FILTER(
                                _CALENDAR,
                                __dt_ <= _CALENDAR[Date] && _CALENDAR[Date] <= __dt
                                    && _CALENDAR[Working Day]
                            )
                        )
        )
    )
  • siumui's avatar
    siumui
    Icon for Helper I rankHelper I
    2 years ago

    Hi ThxAlot,

     

    Saying thank you very much to you is simply not enough!  I truly appreciate and being grateful for your help.  It took me a while to create a dynamic calendar with working day and so forth.  Your codes works like a charm!  It turns out I need to count consecutive business days and your codes did it!!!  

     

    Thank you so much for everything!  I have marked your codes as accepted solution.  Have a wonderful day!!  I hope you will continue to help others who need help just like me, getting stuck working on for a couple days and hitting dead end!  And you save the day!!!  THANK YOU!!!!