Forum Discussion
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:
Thank you so much for any tips!
Anonymous find the attached pbix provided by you and the measure performs as expected, not sure what is the issue here
10 Replies
Hi,
Here is one way to do this:
Example data:Dax:
LatestDate =var Cdate = SELECTEDVALUE(DateToLook[DateToLook]) returnCALCULATE(MAX(DateToGet[DateToGet]),ALL(DateToGet[DateToGet]),DateToGet[DateToGet]<Cdate)
End result:
I hope this helps and if it does consider accepting this as a solution and liking the post!Thanks ValtteriN I tried your suggestion but I received the following error message:
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?
Anonymous provide sample data and expected output,betetr still provide a sample pbix. uplaod in 1/g drive and share the link here.
Thanks smpa01 here's link to my Sample Files
Anonymous just so I understand, you want to bring the closest
Payments[Due Date]to the following table?
PaycycleDatesAnonymous 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
