DAX Consecutive days based on a flag

[Anonymous]

Im trying to get a consecutive days count on data for each month and year. The data schema looks like this:

DATE - Flag
01/01/2021 - 1
01/02/2021 - 1
01/03/2021 - 0
01/04/2021 - 0 
01/05/2021 - 1
01/06/2021 - 0
01/07/2021 - 1
----------------------------
Basically I just need a the MAX consecutive days that a flag of 0 was enabled. So for the data above, it would show 2, since the largest date range with a 0 flag is 2 (jan 3 and 4).

Output would be
January 2021 - 2
February 2021 - X
March 2021 - Y 
Any suggestions?
 

[CNENFRNL · Accepted solution]

Such an operation involves recursion, which is unnecessarily complex. Power Query does the trick easily.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNDJR0lQ6VYHYiQEaaQMUzIAC5kgilkiqnRDFOVOaYqC0whSwwhQwMkoVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, Flag = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Flag"}, {{"ar", each _}, {"Count", each Table.RowCount(_), Int64.Type}}, 0),
    #"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"DATE"}, {"DATE"})
in
    #"Expanded ar"

[Anonymous]

Hi @Anonymous,

Did the above suggestions and expressions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements to find it more quickly.

If these also not help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Jihwan_Kim · Accepted solution]

MAX consecutive days that a flag of 0 =

VAR _newtable =

FILTER (

ADDCOLUMNS (

SUMMARIZE ( Data, Dates[Date], Data[Flag] ),

"@cumulate_flag",

CALCULATE (

CALCULATE (

SUM ( Data[Flag] ),

ALL ( Data[Flag] ),

VAR _currentdates =

MAX ( Dates[Date] )

VAR _currentmonth =

MAX ( Dates[End of Month] )

RETURN

FILTER (

ALL ( Dates[Date], Dates[End of Month] ),

Dates[Date] <= _currentdates

&& Dates[End of Month] = _currentmonth

)

)

)

),

Data[Flag] = 0

)

VAR _groupbyrowcount =

GROUPBY (

_newtable,

[@cumulate_flag],

"@rowcount", SUMX ( CURRENTGROUP (), 1 )

)

RETURN

IF (

HASONEVALUE ( Dates[Month & Year] ),

MAXX ( _groupbyrowcount, [@rowcount] )

)

https://www.dropbox.com/s/7esf0cdn2h1zbh7/zdub.pbix?dl=0 

[CNENFRNL · Accepted solution]

Such an operation involves recursion, which is unnecessarily complex. Power Query does the trick easily.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNDJR0lQ6VYHYiQEaaQMUzIAC5kgilkiqnRDFOVOaYqC0whSwwhQwMkoVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, Flag = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Flag"}, {{"ar", each _}, {"Count", each Table.RowCount(_), Int64.Type}}, 0),
    #"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"DATE"}, {"DATE"})
in
    #"Expanded ar"