topic Find the most recent date in DAX Commands and Tips

Find the most recent date

Anonymous — Wed, 05 Jan 2022 14:50:33 GMT

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!

Re: Find the most recent date

ValtteriN — Wed, 05 Jan 2022 15:28:08 GMT

Hi,

Here is one way to do this:

Example data:

Dax:

LatestDate =

var Cdate = SELECTEDVALUE(DateToLook[DateToLook]) return

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

Re: Find the most recent date

smpa01 — Wed, 05 Jan 2022 15:47:53 GMT

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

Re: Find the most recent date

Anonymous — Wed, 05 Jan 2022 16:44:45 GMT

Thanks smpa01 here's link to my Sample Files

Re: Find the most recent date

Anonymous — Wed, 05 Jan 2022 16:47:08 GMT

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

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

Re: Find the most recent date

smpa01 — Wed, 05 Jan 2022 16:55:24 GMT

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

Payments[Due Date]

to the following table?

PaycycleDates

Re: Find the most recent date

Anonymous — Wed, 05 Jan 2022 17:16:53 GMT

@smpa01 Yes that's correct!

Re: Find the most recent date

ValtteriN — Wed, 05 Jan 2022 17:36:26 GMT

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?

Re: Find the most recent date

smpa01 — Wed, 05 Jan 2022 17:37:16 GMT

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

Re: Find the most recent date

Anonymous — Thu, 06 Jan 2022 18:24:26 GMT

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!

Re: Find the most recent date

smpa01 — Thu, 06 Jan 2022 18:37:49 GMT

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