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 days -> reset to 1 if not consecutive (i tried with earlier but not verfy familiar)

- count sequence

 

In excel, i reproduced this table :

 

 


Any help would be grateful

 

Rhaly

  • 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

7 Replies

  • 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

    • Rhaly's avatar
      Rhaly
      Frequent Visitor
      5 years ago

      Thank you for your blog. It's very instructive 🙂

  • Rhaly's avatar
    Rhaly
    Frequent Visitor
    5 years ago

    Hey,

     

    Sorry to bother you again. The 2 measure works very well.

     

    I need now to add a parameter --> On the count measure, i need to not count when it's a sunday and add 1 if both saturday and monday have occurence.

     

    I understood like 80% of the count measure but i can see where i can add this condition.

     

    Any help will be gratefull 🙂

     

    It would be like this in excel :

     

     

  • nleuck_101's avatar
    nleuck_101
    Continued Contributor
    1 year ago

    Hello,

    Is there a way to exclude weekends? I've tried using WEEKDAYS not in 1 and 7 but it didn't work. Also my Sequence works just fine but not the Count. My count originally came over as a date. So I only get the count of 1 for each sequence. My count is the column on the right.