Find earliest value in column

[Ben81]

Hi all, 

I'm having some trouble finding a solution to this, any help?

I have a table that looks something like below and all I want to do is add a flag of 1 from the earliest date until the first instance of Completed at which point it should change to 0 for every record after this date.

DATE	Stage
29/09/2022	In Progress
10/04/2022	In Progress
10/04/2022	Completed
10/04/2022	In Progress
09/04/2022	In Progress
08/04/2022	Closed
07/04/2022	In Progress
06/04/2022	In Progress
05/04/2022	In Progress

So end result should be ....

DATE	Stage	Flag
29/09/2022	In Progress	0
10/04/2022	In Progress	0
10/04/2022	Completed	1
10/04/2022	In Progress	1
09/04/2022	In Progress	1
08/04/2022	Closed	1
07/04/2022	In Progress	1
06/04/2022	In Progress	1
05/04/2022	In Progress	1

[m_alireza · Accepted solution]

@Ben81 ,

Yes, it is possible. 

you can:
1. add column that finds the min date of when stage = completed

2. 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 

[m_alireza]

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.

[Ben81]

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