Creating a calculated column for count of consecutive days in the data

[johnyip]

Hello anyone in the community. I have encountered an issue regarding counting consceutive days.

I have make reference to the following post.
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-Count-consecutive-days-and-seque...

But this does not give what I want.

For elaboration, below is some of the sample data I am using.

Date	Weekday	extendable_date flag	Extendable date duration	Final day in date duration
4-Dec-2021	Saturday
5-Dec-2021	Sunday	Yes	1	Yes
6-Dec-2021	Monday
7-Dec-2021	Tuesday
8-Dec-2021	Wednesday
9-Dec-2021	Thursday
10-Dec-2021	Friday
11-Dec-2021	Saturday
12-Dec-2021	Sunday	Yes	1	Yes
13-Dec-2021	Monday
14-Dec-2021	Tuesday
15-Dec-2021	Wednesday
16-Dec-2021	Thursday
17-Dec-2021	Friday
18-Dec-2021	Saturday
19-Dec-2021	Sunday	Yes	1	Yes
20-Dec-2021	Monday
21-Dec-2021	Tuesday
22-Dec-2021	Wednesday
23-Dec-2021	Thursday	Yes	12
24-Dec-2021	Friday	Yes	12
25-Dec-2021	Saturday	Yes	12
26-Dec-2021	Sunday	Yes	12
27-Dec-2021	Monday	Yes	12
28-Dec-2021	Tuesday	Yes	12
29-Dec-2021	Wednesday	Yes	12
30-Dec-2021	Thursday	Yes	12
31-Dec-2021	Friday	Yes	12
1-Jan-2022	Saturday	Yes	12
2-Jan-2022	Sunday	Yes	12
3-Jan-2022	Monday	Yes	12	Yes
4-Jan-2022	Tuesday

The Extendable date duration is the calculated column I would like to add in the data. From the above link and some other information, the solutions suggested are incremental from 1 to the final answer (i.e. for the case of 23 Dec 2021 to 3 Jan 2022, the soultion gives value of 1,2,3,.... ,12 instead of straight 12s that I need as stated above.

Also would like to have a column indicating if the day is final day of the duration as shown.

Anyone have an idea as to how to achieve my desired result? In case a calculated column won't work, I would like to know if that is achievable in PowerQuery.

I have attached the pbix file herein with the exact same structure of the data I am using.

[Greg_Deckler · Accepted solution]

@johnyip I did this using 2 columns, pretty certain I could get it down to a single column but posting here as soon as possible as a solution. PBIX attached below signature. I updated this to have a single column version as well.

Cthulhu Column = 
    VAR __tmpTable1 = FILTER('Table',[extendable_date flag] = EARLIER([extendable_date flag]) && [Index] < EARLIER([Index])) 
    VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table'),[Index]<EARLIER([Index]) && [extendable_date flag]=EARLIER([extendable_date flag])),[Index])) 
    VAR __max = MAXX(__tmpTable2,[Index]) 
    VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index])
    VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart) 
    VAR __Result = IF(ISBLANK(__max),1,IF(__max=[Index]-1,COUNTROWS(__tmpTable3)+1,1))
RETURN
    __Result



Cthulhu Streak = 
    VAR __Group = [extendable_date flag]
    VAR __Min = MINX( FILTER( 'Table', [extendable_date flag] <> __Group && [Index] > EARLIER( [Index] ) ), [Index] )
    VAR __MinValue = MINX( FILTER( 'Table', [Index] = __Min - 1 ), [Cthulhu Column] )
    VAR __Result = IF( __Group = "Yes", __MinValue, BLANK() )
RETURN
    __Result

Double Cthulhu Column = 
    VAR __Index = [Index]
    VAR __Table1 = 
        ADDCOLUMNS(
            'Table',
            "__Cthulhu",
                VAR __CurrIndex = [Index]
                VAR __CurrGroup = [extendable_date flag]
                VAR __LocalGroupStart = MAXX( FILTER( 'Table', [Index] < __CurrIndex && [extendable_date flag] <> __CurrGroup ), [Index] )
                VAR __Result = __CurrIndex - __LocalGroupStart
            RETURN
                __Result
        )
    VAR __Table2 = 
        ADDCOLUMNS(
            __Table1,
            "__Longest",
                VAR __Group = [extendable_date flag]
                VAR __Min = MINX( FILTER( __Table1, [extendable_date flag] <> __Group && [Index] > EARLIER( [Index] ) ), [Index] )
                VAR __MinValue = MINX( FILTER( __Table1, [Index] = __Min - 1 ), [__Cthulhu] )
                VAR __Result = IF( __Group = "Yes", __MinValue, BLANK() )
            RETURN
                __Result
        )
    VAR __Result = MAXX( FILTER( __Table2, [Index] = __Index ), [__Longest] )
RETURN
    __Result

[ThxAlot]

Easy enough. As Date column contains consecutive dates, it's already ordinal and index is redundant.

Extendable_date streak = 
IF(
    DATA[extendable_date flag] = "Yes",
    VAR __dt = DATA[Date]
    RETURN
        CALCULATE(
            MIN( DATA[Date] ),
            DATA[Date] > __dt,
            DATA[extendable_date flag] <> "Yes",
            ALL()
        )
            - CALCULATE(
                MAX( DATA[Date] ),
                DATA[Date] < __dt,
                DATA[extendable_date flag] <> "Yes",
                ALL()
            ) - 1
)

Final of streak = 
IF(
    DATA[extendable_date flag] = "YES",
    VAR __dt = DATA[Date]
    RETURN
        IF(
            CALCULATE(
                MIN( DATA[Date] ),
                DATA[Date] > __dt,
                DATA[extendable_date flag] <> "Yes",
                ALL()
            ) - 1 = __dt,
            "Y"
        )
)

[johnyip · Accepted solution]

@Greg_Deckler Brilliant. Both your answer of double column and 2-columns-version answers does provide a great help. With some slight modifications to my dataset I can easily adopt the solutions given.

The Double Cthulhu Column is the direct working solution to my question, and for the 2-columns-version answer, with some tweaks, it is the solution to the follow-up questions that I have added later on (when you were working on my original question, I suppose) via my edition to the thread.

First or last day of extendable date = 
    VAR __tmpTable1 = FILTER('Table',[extendable_date flag] = EARLIER([extendable_date flag]) && [Index] < EARLIER([Index])) 
    VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table'),[Index]<EARLIER([Index]) && [extendable_date flag]=EARLIER([extendable_date flag])),[Index])) 
    VAR __max = MAXX(__tmpTable2,[Index]) 
    VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index])
    VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart) 
    VAR __Result = IF(ISBLANK(__max),1,IF(__max=[Index]-1,COUNTROWS(__tmpTable3)+1,1))
RETURN
    IF(__Result=1 && 'Date'[extendable_date flag]="Yes","First day",IF(__Result='Table'[Double Cthulhu Column],"Last day"))

Really thanks a lot, it does help me A LOT. 😀

[Greg_Deckler · Accepted solution]

@johnyip I did this using 2 columns, pretty certain I could get it down to a single column but posting here as soon as possible as a solution. PBIX attached below signature. I updated this to have a single column version as well.

Cthulhu Column = 
    VAR __tmpTable1 = FILTER('Table',[extendable_date flag] = EARLIER([extendable_date flag]) && [Index] < EARLIER([Index])) 
    VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table'),[Index]<EARLIER([Index]) && [extendable_date flag]=EARLIER([extendable_date flag])),[Index])) 
    VAR __max = MAXX(__tmpTable2,[Index]) 
    VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index])
    VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart) 
    VAR __Result = IF(ISBLANK(__max),1,IF(__max=[Index]-1,COUNTROWS(__tmpTable3)+1,1))
RETURN
    __Result



Cthulhu Streak = 
    VAR __Group = [extendable_date flag]
    VAR __Min = MINX( FILTER( 'Table', [extendable_date flag] <> __Group && [Index] > EARLIER( [Index] ) ), [Index] )
    VAR __MinValue = MINX( FILTER( 'Table', [Index] = __Min - 1 ), [Cthulhu Column] )
    VAR __Result = IF( __Group = "Yes", __MinValue, BLANK() )
RETURN
    __Result

Double Cthulhu Column = 
    VAR __Index = [Index]
    VAR __Table1 = 
        ADDCOLUMNS(
            'Table',
            "__Cthulhu",
                VAR __CurrIndex = [Index]
                VAR __CurrGroup = [extendable_date flag]
                VAR __LocalGroupStart = MAXX( FILTER( 'Table', [Index] < __CurrIndex && [extendable_date flag] <> __CurrGroup ), [Index] )
                VAR __Result = __CurrIndex - __LocalGroupStart
            RETURN
                __Result
        )
    VAR __Table2 = 
        ADDCOLUMNS(
            __Table1,
            "__Longest",
                VAR __Group = [extendable_date flag]
                VAR __Min = MINX( FILTER( __Table1, [extendable_date flag] <> __Group && [Index] > EARLIER( [Index] ) ), [Index] )
                VAR __MinValue = MINX( FILTER( __Table1, [Index] = __Min - 1 ), [__Cthulhu] )
                VAR __Result = IF( __Group = "Yes", __MinValue, BLANK() )
            RETURN
                __Result
        )
    VAR __Result = MAXX( FILTER( __Table2, [Index] = __Index ), [__Longest] )
RETURN
    __Result