Forum Discussion
Filtering Consecutive Dates by a Measure
hello AMONT
Glad it is worked as intended.
if you need to limit certain id, you can add more condition inside the filter.
for example:
MAXX(
FILTER(
ALL('Table'),
'Table'[DateOnly]<_Select&&
'Table'[Well_ID]=_ID *** adding at top var _ID = SELECTEDVALUE('Table'[Well_ID])***
),
'Table'[DateOnly]
)or directly use should have same result :
MAXX(
FILTER(
ALL('Table'),
'Table'[DateOnly]<_Select&&
'Table'[Well_ID]=SELECTEDVALUE('Table'[Well_ID])
),
'Table'[DateOnly]
)This way, the measure only calculate for previous date AND for same ID.
you can add more filter condition as you need as above.
Hope this will help.
Thank you.
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
)2. create a new measure for calculating the first date of consecutive dateMeasure 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]
)3. create a new measure to define duplicate because we dont want to see many same value in between consecutive dateMeasure Remove Duplicate =
IF(
[Measure Min DateOnly]=SELECTEDVALUE('Table'[DateOnly]),
1,
0
)4. Plot the mindate and nextdate of consecutive date then put the removeduplicate measure as visual filter5. create a new measure for calculating number of consecutive date.
Measure Count =
DATEDIFF(
[Measure Min DateOnly],
[Measure Next Date],
DAY
)Detail is in attached pbix.Hope this will help.Thank you.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.
hello AMONT
i think if [measure total days with exceed]is a measure, then you should have no issue.
The SUM in [measure total days with exceed] in my pbix is meant to make [measure total days with exceed] behaves as measure.
You can do your [measure total days with exceed] measure to continue your calculation.
If you still have wrong result perhaps you have another calculation that affect your final desired outcome.
Please share your pbix contained of a sample of your original data so we can work it out to achive your desired outcome.
Please remove any confidential information.
Thank you.