Forum Discussion

fmatioli06's avatar
fmatioli06
Frequent Visitor
8 years ago
Solved

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

IDNameDateExpectedBudget NeedGoal MetDate of Goal Met
200000Bob Jones11/9/2016$3,976.95$6,500.00  
200000Bob Jones12/13/2016$4,601.96$6,500.00  
200000Bob Jones1/11/2017$5,396.33$6,500.00  
200000Bob Jones2/14/2017$5,429.14$6,500.00  
200000Bob Jones3/15/2017$6,500.00$6,500.00Completed3/15/2017
200000Bob Jones4/11/2017$6,600.00$6,500.00Completed3/15/2017
Need Help

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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])))
  • fmatioli06's avatar
    fmatioli06
    Frequent Visitor
    8 years ago

    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!!

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      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 )
      • fmatioli06's avatar
        fmatioli06
        Frequent Visitor
        8 years ago

        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.