topic Re: [Dax] Count consecutive days and sequence in DAX Commands and Tips

[Dax] Count consecutive days and sequence

Rhaly — Sun, 08 Nov 2020 20:52:39 GMT

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

Re: [Dax] Count consecutive days and sequence

wdx223_Daniel — Mon, 09 Nov 2020 01:08:44 GMT

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

Re: [Dax] Count consecutive days and sequence

amitchandak — Mon, 09 Nov 2020 01:24:50 GMT

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-Day-Break/ba-p/1290346

Re: [Dax] Count consecutive days and sequence

Rhaly — Mon, 09 Nov 2020 08:02:53 GMT

Thank you so much 🙂 it works very well

Re: [Dax] Count consecutive days and sequence

Rhaly — Mon, 09 Nov 2020 08:06:20 GMT

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

Re: [Dax] Count consecutive days and sequence

Rhaly — Wed, 18 Nov 2020 19:10:20 GMT

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 :

Re: [Dax] Count consecutive days and sequence

Anonymous — Tue, 23 Aug 2022 10:53:32 GMT

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

Re: [Dax] Count consecutive days and sequence

nleuck_101 — Wed, 18 Sep 2024 19:55:06 GMT

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.