cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Find earliest Date from column with possible repeated row value

Hi All,

I would really appreciate any DAX help I can get with this.

I am trying to create a calculated column that finds the first date when the "Goal Met" column shows "completed" or in other words, when the "Expected" matches or exceeds the "Budget Need".

I have tried: Column = CALCULATE ( MIN ( Table[Date] ), Table[Goal Met] = "Completed" ), but because there could be multiple rows with "completed", I can't figure out a way to get the first date that the "Goal Met" column shows "completed".

Below is an example of what I mean, with the outcome I am trying to get to in red

 ID Name Date Expected Budget Need Goal Met Date of Goal Met 200000 Bob Jones 11/9/2016 \$3,976.95 \$6,500.00 200000 Bob Jones 12/13/2016 \$4,601.96 \$6,500.00 200000 Bob Jones 1/11/2017 \$5,396.33 \$6,500.00 200000 Bob Jones 2/14/2017 \$5,429.14 \$6,500.00 200000 Bob Jones 3/15/2017 \$6,500.00 \$6,500.00 Completed 3/15/2017 200000 Bob Jones 4/11/2017 \$6,600.00 \$6,500.00 Completed 3/15/2017
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I

@fmatioli06

It works when I add dummy ID to your sample data

Please see the pic below

Regards,
Zubair

8 REPLIES 8
Highlighted
Super User IV

Don't have time at the moment for full solution but looks like you want FIRSTNONBLANK.

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Highlighted
Continued Contributor
Highlighted
Solution Sage

Try the following Dax and see if it get the results you want

`Column = IF(Table[Goal Met]="Completed",CALCULATE(FIRSTNONBLANK(Table[Date],1),ALLEXCEPT(Table,Table[Goal Met])))`
Highlighted
Frequent Visitor

Thank you for the quick responses.

One thing I forgot to mention is that the table includes multiple IDs and multiple lines for each ID, so using FIRSTNONBLANK like you all suggested gives me the earliest date of the first time the row with "completed" happened, so if I want to filter and look at a specific ID they all end up having the same "date of Goal met".

I need the earliest date for each unique ID. Does that make sense?

Thank you again!!

Highlighted
Super User I

@fmatioli06

Give this a shot

```Date of Goal Met =
VAR First_Date =
CALCULATE (
FIRSTNONBLANK ( Table1[Date], 1 ),
FILTER ( ALLEXCEPT ( Table1, Table1[ID] ), Table1[Goal Met] = "Completed" )
)
RETURN
IF ( Table1[Goal Met] = "Completed", First_Date )```
Regards,
Zubair

Highlighted
Frequent Visitor

Thanks Zubair,

That's still not giving me the Date for each ID, it populated all the ID's with the same date which is the earliest first occurence of "Completed".

Reading your formula it makes sense that it would work but there's something missing that I can't seem to figure out.

Highlighted
Super User I

@fmatioli06

It works when I add dummy ID to your sample data

Please see the pic below

Regards,
Zubair

Highlighted
Frequent Visitor

wow that's great, thank you Zubair!

I think my issue is with the data, I need to dig deeper, but this will work!

Thank for taking the time to help me!!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors