Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Rhaly
Frequent Visitor

[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 :

 

Rhaly_0-1604868745298.png

 


Any help would be grateful

 

Rhaly

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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

View solution in original post

7 REPLIES 7
nleuck_101
Responsive Resident
Responsive Resident

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.

 

Screenshot 2024-09-18 155057.png

Rhaly
Frequent Visitor

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 :

 

Rhaly_0-1605726609577.png

 

amitchandak
Super User
Super User

@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-...

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

wdx223_Daniel
Super User
Super User

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
Not applicable

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

dbs123_0-1661251942562.png

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 

Thank you so much 🙂 it works very well

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.