cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cbutler
Helper III
Helper III

How to return the latest text value depending on a date range

Hi all, 

I can't work out how to return the latest text value in my table depending on a date range. So my table is showing PPE concernes from providers, they will not anser this question each time just complete if the answer changes - I want to show the last text input

eg 

Provider nameDateAnswer (Yes/No)
A01/05/2020Yes
A02/05/2020 
A03/05/2020 
A04/05/2020No
A05/05/2020Yes 
B01/05/2020 

B

02/05/2020No
B03/05/2020 
B04/05/2020Yes
B05/05/2020 
C01/05/2020No
C02/05/2020 
C03/05/2020Yes
C04/05/2020No
C05/05/2020 

 

So.....
if date range selected is 01/05/2020 to 05/05/2020 then Provder A = Yes, B = Yes, C = No.
If date range selected is 01/05/2020 to 03/05/2020 then Provider A = Yes, B = No, C = Yes.
If date range selected is 01/05/2020 to 01/05/2020 then Provider A = Yes, B = "Blank/Not completed", C = No

Can anyone help me please......?

5 REPLIES 5
Greg_Deckler
Super User IV
Super User IV

I believe you want Lookup Min/Max: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

 

Or LookupValue Range: https://community.powerbi.com/t5/Quick-Measures-Gallery/LOOKUPVALUE-Range/m-p/974201#M430


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

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




@Cbutler , try like

LastNONBLANKVALUE(Table[Date],Max(Table[Answer])

or

maxx(summarize(table,table[Provider name],"_1",LastNONBLANKVALUE(Table[Date],Max(Table[Answer]))),[_1])

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak  can you tell me how to display the "date" of the last non blank value from the same table? 

@Cbutler That's the Lookup Min/Max pattern I sent, https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434


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

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




Still not able to return the date for the last nonblank value 😞

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors