Filtering Consecutive Dates by a Measure

hello AMONT 

i am not sure but the second value is written 6/24/24-10/24/24, but from your data it should be 6/24/24-10/20/24

Regardless, please check if this accomodate your need.

you can do this with PQ or DAX. 

- using PQ

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddhLjhwhEATQq1iztkSTQAG+iuX7X8OFPz1y+aU0q9j0RNXrAPX37x+jxOv+i/7l9e31+vj68fr48fVXXP+J6984HDfH3fFwfDmejpfjzbi9HKvlVZyq+lXU/CoqfhX1vopqX0Wtr6LSV1Hnu4U633HS2fWq+1UXrG5YXbFO0Lvj5Xgzpt/r6fcdh+Pm2C3p97Lfy34v+73s97LfSaiTUCehTkKdhDoJdRLqJNRJqNNQp6FOQ52GOg11Guo01PmE+o5dsrpluCWHdnpop4d2Guo01Gmo01CnoU5DnRlUDu2i30W/i34X/S76XfS76HfR76LfZb/Lfpf9Lvtd9rvsd9nvst9lv8t+l/0u+132u+x32e+y32W/y36X/S77Xfa77HfT76bfTb+bfjf9bvrd9Lvpd9Pvtt9tv9t+t/1u+932u+132++2322/2363/W773fa77Xfb77bfbb/bfrf9bvu9X7BanvfuWC3vWC3vWC3vWC3vWC3vWC3vWC1Pnaxm0pNoT540JduTJ10J9+RJW9I9edKXeE+ua+7Jdc89uS66J+9JPpL8SnJd6U+uO/3Jdak/AHWrPzn71pLEfAq18CHUwmdQCx9BLXwCtfABVPOu5l0T3jXhXRPeNeFdE9414V0T3jXhXRPeNeFdE97V43zypC/n+eRJXw70yZO+nOiTJ3050gcg+4ZXOrzS4ZUOr3SYcZhxmHEUfo2j8FscT8afOb+u8WT8mfMLG0/Gn3nStSZl+UPEyZO6/Cni1z+f5Elfr3QkKx3JSkey0pGsdCQrHclKR7LSkTLmbfnPSI9nGkzbP+mfT/xN+790ML2YTqaL6Wb6G/X/cXUcjt2vumB1w+qK1R2rS1a3DLcMtwy3DLcMt4xhH5fj6Xg53lb2ciypQb9Bv/Hw+zftTNU7impHUesoKh1FncN+w37DfsN+4+n3/ZFuWF2xumN1yeqWoTcbT7/v2C8x/BbDLek37DfsN+y3kV4rejeN09k4nY3T2UivcTobp7NxOtuT3ruc21W9lPak9471UprpNdNrptdMr5leM71mes30muk102um10yvmV7L6HE6m6ezeTo7/XZOZ+d0dk5n53R2+u2czs7p7JzObr/dfrv9dvvt9tt99Hcf/d1Hf/fR3330dx/93Ud/99HfffR3H/396fcdu2W4ZbhluGVTy1FUcnBoB4d2cGgHh3bwjjo4tINDOzi0w2f8MNRhqMNQh6EOD+3w0A4P7fDQDg/teEJ9x27JoR0e2uGhHR7a4aEdHtrhoR2A+uMn", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateOnly = _t, #"Total Days With 3 Exceed Ref" = _t]),
#"Extracted Text Before Delimiter" = Table.TransformColumns(Source, {{"DateOnly", each Text.BeforeDelimiter(_, " "), type text}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter", {{"DateOnly", type date}}, "en-US"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"DateOnly", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"DateOnly", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Total Days With 3 Exceed Ref"}, {"Added Index1.Total Days With 3 Exceed Ref"}),
#"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "Custom", each if [Total Days With 3 Exceed Ref]<>[Added Index1.Total Days With 3 Exceed Ref] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1) and ([Added Index1.Total Days With 3 Exceed Ref] <> null)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"DateOnly", "Total Days With 3 Exceed Ref"}),
#"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "Start", each if [Total Days With 3 Exceed Ref]="1" then [DateOnly] else null),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"Start"}),
#"Added Custom2" = Table.AddColumn(#"Filled Down", "End", each if [Total Days With 3 Exceed Ref]="0" then [DateOnly]-1 else null),
#"Filled Up" = Table.FillUp(#"Added Custom2",{"End"}),
#"Removed Duplicates" = Table.Distinct(#"Filled Up", {"Start"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Duplicates",{{"Start", type date}, {"End", type date}}),
#"Removed Other Columns1" = Table.SelectColumns(#"Changed Type1",{"Start", "End"})
in
#"Removed Other Columns1"

- using DA

create a new calculated column to get next date value.

Next Value =
var _Date =
MAXX(
    FILTER(
        'Table',
        'Table'[DateOnly]<EARLIER('Table'[DateOnly])
    ),
    'Table'[DateOnly]
)
Return
MAXX(
    FILTER(
        'Table',
        'Table'[DateOnly]=_Date
    ),
    'Table'[Total Days With 3 Exceed Ref]
)

DAX =
var _Sum =
SUMMARIZE(
    FILTER(
        'Table',
        'Table'[Total Days With 3 Exceed Ref]<>'Table'[Next Value]
    ),
    'Table'[DateOnly],
    'Table'[Total Days With 3 Exceed Ref],
    'Table'[Next Value]
)
var _Next = 
ADDCOLUMNS(
    ADDCOLUMNS(
        _Sum,
        "Next Date1",
        MINX(
            FILTER(
                _Sum,
                'Table'[DateOnly]>=EARLIER('Table'[DateOnly])&&
                'Table'[Next Value]=1
            ),
            'Table'[DateOnly]
        )
    ),
    "Datediff",
    DATEDIFF(
        [DateOnly],
        [Next Date1],
        DAY
    )
)
Return
SELECTCOLUMNS(
    FILTER(
        _Next,
        [Datediff]>0
    ),
    "Start",[DateOnly],
    "End",[Next Date1]-1
)

Hi AMONT ,

Thank you for reaching out to the Microsoft fabric community forum.

Could you please confirm if the issue has been resolved. I wanted to check if you had the opportunity to review the information provided by Irwan .Please feel free to contact us if you have any further questions.

Thank you.

Hi AMONT ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.