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, thanks for taking a stab at this!
For the 10/20 vs 10/24, that was me, just a typo, your values are correct.
I went the DAX route first, and I get an error for a circular dependency on the first step. In my dataset, the Total Days With 3 Exceed Ref is a measure, and I think that's what causing it. That measure is the product of three previous measures, which is why I think this has become such a headache for me:
The following measures are what has resulted in Total Days with 3 Exceed Ref:
The first measure summarizes the column Exceeds Ref, which can have values ranging from 0 to 36, down to 0 to 3, as I have the hours filtered in my dataset.
hello @AMONT
here is the tweak if [Total Days With 3 Exceed Ref] as a measure.
you can continue this below step from creating measure for [Total Days With 3 Exceed Ref].
Right side of above screenshot is using [Total Days With 3 Exceed Ref] as a measure which has exact same result as using calculated column in left side.
As measure works in filter context, so you need to have 'DateOnly' column as filter or else you will have blank because there is no filter.
1. create a new measure for calculating end of conscutive date
Measure Changed Value =
var _Select = SELECTEDVALUE('Table'[DateOnly])
var _Date =
MAXX(
FILTER(
ALL('Table'),
'Table'[DateOnly]<_Select
),
'Table'[DateOnly]
)
var _Value =
MAXX(
FILTER(
ALL('Table'),
'Table'[DateOnly]=_Date
),
[Measure Total Days With 3 Exceed Ref]
)
Return
IF(
[Measure Total Days With 3 Exceed Ref]<>_Value,
1,
0
)
Measure Min DateOnly =
MINX(
FILTER(
ALL('Table'),
'Table'[DateOnly]>=SELECTEDVALUE('Table'[DateOnly])&&
[Measure Changed Value]=1&&
[Measure Total Days With 3 Exceed Ref]=1
),
'Table'[DateOnly]
)
Measure Remove Duplicate =
IF(
[Measure Min DateOnly]=SELECTEDVALUE('Table'[DateOnly]),
1,
0
)
5. create a new measure for calculating number of consecutive date.
Measure Count =
DATEDIFF(
[Measure Min DateOnly],
[Measure Next Date],
DAY
)
First off, I appreciate all the time you've put into this. I owe you big time! I know this is so close to correct, but I have no idea what I'm doing wrong.
The [Measure Total Days With 3 Exceed Ref] doesn't work in my pbix. I cannot perform the SUM measure since it's not a column in the table, just a measure based on several other measures as described above. As such, I get a weird result in all the other formulas that you provided.
I think I need a measure to mimic your measure, but in the summation of the [Total Days With 3 Exceed Ref] measure that I have. In the Card that I added, it defaults to the sum, so I guess I need that in a measure to make the same result?
One thing to make sure is clear, this is a single well (Well 100) example. I have over 100 wells. So I am assuming I need the measure to be filtered by well number.
Helpful resources
Power BI Monthly Update - September 2025
Check out the September 2025 Power BI update to learn about new features.
Fabric Community Update - August 2025
Find out what's new and trending in the Fabric community.
