cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ben81
Helper I
Helper I

Find earliest value in column

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 StageFlag
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
1 ACCEPTED SOLUTION
m_alireza
Solution Supplier
Solution Supplier

@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 

View solution in original post

3 REPLIES 3
m_alireza
Solution Supplier
Solution Supplier

@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
Solution Supplier
Solution Supplier

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: 
completedscreenshot.png

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.