Forum Discussion

Rhaly's avatar
Rhaly
Frequent Visitor
5 years ago
Solved

[Dax] Count consecutive days and sequence

Hi,   I'm kinda new in Power Pivot and i would ask some help 🙂   I have 2 columns in my table : Date and ID   For each ID, i would like to add 2 columns in power pivot to: - count consecutive...
  • wdx223_Daniel's avatar
    wdx223_Daniel
    5 years ago

    these code might work in calculated columns

    Sequence=
VAR vPrevTbl =
    FILTER (
        Table1,
        Table1[ID] = EARLIER ( Table1[ID] )
            && Table1[Date] <= EARLIER ( Table1[Date] )
    )
VAR vPrevDate =
    ADDCOLUMNS (
        vPrevTbl,
        "d",
            VAR vCurrentDate = Table1[Date]
            VAR vCurrentID = Table1[ID]
            VAR vPrevTbl =
                FILTER ( Table1, Table1[Date] <= vCurrentDate && Table1[ID] = vCurrentID )
            RETURN
                MAXX (
                    FILTER (
                        vPrevTbl,
                        VAR vCD = Table1[Date]
                        VAR r =
                            MAXX ( FILTER ( vPrevTbl, Table1[Date] < vCD ), Table1[Date] )
                        RETURN
                            vcd - 1 <> r
                    ),
                    Table1[Date]
                )
    )
RETURN
    COUNTROWS ( SUMMARIZE ( vprevdate, [d] ) )
    Count=
VAR vCurrentDate = Table1[Date]
VAR vCurrentID = Table1[ID]
VAR vPrevTbl =
    FILTER ( Table1, Table1[Date] <= vCurrentDate && Table1[ID] = vCurrentID )
VAR vPrevDate =
    MAXX (
        FILTER (
            vPrevTbl,
            VAR vCD = Table1[Date]
            VAR r =
                MAXX ( FILTER ( vPrevTbl, Table1[Date] < vCD ), Table1[Date] )
            RETURN
                vcd - 1 <> r
        ),
        Table1[Date]
    )
RETURN
    vCurrentDate - vPrevDate + 1
wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
5 years ago

these code might work in calculated columns

Sequence=
VAR vPrevTbl =
    FILTER (
        Table1,
        Table1[ID] = EARLIER ( Table1[ID] )
            && Table1[Date] <= EARLIER ( Table1[Date] )
    )
VAR vPrevDate =
    ADDCOLUMNS (
        vPrevTbl,
        "d",
            VAR vCurrentDate = Table1[Date]
            VAR vCurrentID = Table1[ID]
            VAR vPrevTbl =
                FILTER ( Table1, Table1[Date] <= vCurrentDate && Table1[ID] = vCurrentID )
            RETURN
                MAXX (
                    FILTER (
                        vPrevTbl,
                        VAR vCD = Table1[Date]
                        VAR r =
                            MAXX ( FILTER ( vPrevTbl, Table1[Date] < vCD ), Table1[Date] )
                        RETURN
                            vcd - 1 <> r
                    ),
                    Table1[Date]
                )
    )
RETURN
    COUNTROWS ( SUMMARIZE ( vprevdate, [d] ) )
Count=
VAR vCurrentDate = Table1[Date]
VAR vCurrentID = Table1[ID]
VAR vPrevTbl =
    FILTER ( Table1, Table1[Date] <= vCurrentDate && Table1[ID] = vCurrentID )
VAR vPrevDate =
    MAXX (
        FILTER (
            vPrevTbl,
            VAR vCD = Table1[Date]
            VAR r =
                MAXX ( FILTER ( vPrevTbl, Table1[Date] < vCD ), Table1[Date] )
            RETURN
                vcd - 1 <> r
        ),
        Table1[Date]
    )
RETURN
    vCurrentDate - vPrevDate + 1
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi, thank you so much for your help! 
    I have a similar challenge but need to ignore weekends.

    on the 11th it is a Monday so it should have 2 instead of 1. 
    I have a calendar table but don't know how to adapt the formula. 

    Thank you so much 

  • Rhaly's avatar
    Rhaly
    Frequent Visitor
    5 years ago

    Thank you so much 🙂 it works very well