cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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 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
1 ACCEPTED SOLUTION
Solution Specialist

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

``````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}}),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"mincompleteddate"})
in
#"Removed Columns"``````

3 REPLIES 3
Solution Specialist

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

``````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}}),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"mincompleteddate"})
in
#"Removed Columns"``````

Solution Specialist

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.

Helper I

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors