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)

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]
                            )
                        )
        )
    )