Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Find the most recent date

Hi all!

I'm very new to Power BI and Dax calculations.  I know this is likely very simple! The examples I'm finding are more complex than I need and are confusing me more than helping.  I'm have two data sources, one is a Reference table of dates (Payment Cycles), the other a list of outgoing payments (Payments).  I need to find the date from the Payment table cycle table that is closest to the date in the Payment Cycle table, but is not after it.    

 

Here's a sample of my data:

 

Example.PNG

 

Thank you so much for any tips!

1 ACCEPTED SOLUTION

@Anonymous  find the attached pbix provided by you and the measure performs as expected, not sure what is the issue here

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

10 REPLIES 10
smpa01
Super User
Super User

@Anonymous  just so I understand, you want to bring the closest 

 

Payments[Due Date]

 

to the following table?

 

PaycycleDates 

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

@smpa01 Yes that's correct!

@Anonymous  you can use this emasure

Measure = 
var _max = MAX(PaycycleDates[Payday])
var _val = CALCULATE(MAX(Payments[Due Date]),FILTER(Payments,Payments[Due Date]<=_max))
return _val

 

smpa01_0-1641404228664.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

@smpa01 Thank you.  This partrially works.  If the date in both tables matches, then it matches perfectly.  However, if the date is in the Payment table, but NOT in the Paycycle table, then I would like to find the date closest to the Paycycle date. Currently, it's just left blank.  Thank you!

 

@Anonymous  find the attached pbix provided by you and the measure performs as expected, not sure what is the issue here

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
smpa01
Super User
Super User

@Anonymous  provide sample data and expected output,betetr still provide a sample pbix. uplaod in 1/g drive and share the link here.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

Thanks @smpa01 here's  link to my  Sample Files 

ValtteriN
Super User
Super User

Hi,

Here is one way to do this:

Example data:

ValtteriN_0-1641396395943.png

ValtteriN_1-1641396409713.png

 

Dax:

LatestDate =
var Cdate = SELECTEDVALUE(DateToLook[DateToLook]) return

CALCULATE(MAX(DateToGet[DateToGet]),ALL(DateToGet[DateToGet]),DateToGet[DateToGet]<Cdate)

End result:
ValtteriN_2-1641396465583.png

 


I hope this helps and if it does consider accepting this as a solution and liking the post!




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

Proud to be a Super User!




Anonymous
Not applicable

Thanks @ValtteriN I tried your suggestion but I received the following error message:

Error.PNG

here's  link to my  Sample Files in case this helps. 

Hi,

The Dax in my example was for a measure. If you place it within a column that type of error might occur due to reason explained here in SQLBI's post: https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

Is it possible to use a measure instead of a column in your model? 





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

Proud to be a Super User!




Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.