Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
It works when I add dummy ID to your sample data
Please see the pic below
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!!
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 )
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.
It works when I add dummy ID to your sample data
Please see the pic below
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!!
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])))
Check this link and you should be able to adopt it for your case:
Selecting the first record with same values in another column in Power BI
Don't have time at the moment for full solution but looks like you want FIRSTNONBLANK.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |