[Dax] Count consecutive days and sequence

[Rhaly]

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

[nleuck_101]

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.

[Rhaly]

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 :

[amitchandak]

@Rhaly , refer to my blog on Continous Streak can help

https://community.powerbi.com/t5/Community-Blog-Staging-Private/Power-BI-Continuous-Streak-With-One-...

[Rhaly]

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

[wdx223_Daniel · Accepted solution]

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]

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]

Thank you so much 🙂 it works very well