Forum Discussion
Find earliest value in column
- 3 years ago
Ben81 ,
Yes, it is possible.
you can:
1. add column that finds the min date of when stage = completed2. add conditional column that compares the list of dates against the column created in step 1. If less than or equal to min date of stage = completed, 1, otherwise, 0.
3. delete column created in step 1 as no longer necessary.
You can copy the query below in your advanced editor and adjust as needed for your actual table:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrLUN7DUNzIwMlLSUfLMUwgoyk8vSi0uVorViVYyNNA3MCFK0jk/tyAntSQ1hbA+oHV4JC2QDc3JL4aaaGCOT5MZPklTPJIovkfyQywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, Stage = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"Stage", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "mincompleteddate", each List.Min(Table.SelectRows(#"Changed Type",(x)=>x[Stage]="Completed")[DATE])), #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "flag", each if [DATE] <= [mincompleteddate] then 1 else 0), #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"mincompleteddate"}) in #"Removed Columns"
I also added a link to the sample pbix file for your reference:
https://drive.google.com/file/d/1AagVmdR8bB1Ghe5fkK0-wyEPdpO0QSjy/view?usp=sharing
Hi Ben81 ,
Have a question about your expected end result. If you have multiple Stages on the same date as the first instance of Completed, should they be marked as 1 or 0? In your example, you had one "In Progress" status with a Flag of `1 and another with 0, despite both of them being on the same date (10/04/2022) as the Completed Stage. So I am not sure what you expect...
The calculated column below will mark all dates that are on or before the first instance of Completed as 1:
VAR _FilterCompleted =
FILTER (
'Table',
'Table'[Stage] = "Completed"
)
RETURN
IF( 'Table'[Date] <= CALCULATE ( MIN ( 'Table'[Date] ), _FilterCompleted ),1,0)Sample output:
Let me know if you expect something else and I will modify the formula accordingly.
I have the tabled rolled up so there will only be one stage per date and also this is something I need to do in power query M language and not in DAX I'm afraid.
Anyway this could be made to work that way?
Thanks,
Ben