Forum Discussion
Solved
Filtering Consecutive Dates by a Measure
Apologies if this has been posted before, as I do see a lot of consecutive date topics here. I have groundwater well data that has been assigned a 1 or a 0, depending on if certain parameters ar...
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]
)
then create a new table to summarize the original table
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
)
You can tweak the code to your preferences
Sorry if the code is quite messy but hope you can get the idea where to start.
Hope this will help.
Thank you.
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.
Total Exceeds Ref =
ROUND(SUMX (
VALUES ('All Stations'[Hour Start]),
AVERAGE ('All Stations'[Exceeds Ref])
),0)
Ref Count = IF([Total Exceeds Ref] = 3, 1, 0) --> Assigns a value so that only the Total Exceeds Ref that equal 3 are counted.
Total Days With 3 Exceed Ref =
SUMX(
VALUES('All Stations'[DateOnly]),
VAR DayCount =
CALCULATE(
[Ref Count],
ALLEXCEPT('All Stations', 'All Stations'[DateOnly], 'All Stations'[Well_Number])
)
RETURN IF(DayCount = 1, 1, 0)
)
This measure is what allowed me to get the total days that a well has the value of 1 and be able to summarize in a visual.