Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX Consecutive days based on a flag

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?
 

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    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"

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

     

    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] )
    )
     
     
     
     

3 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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"

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

     

    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] )
    )
     
     
     
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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