DAX for "last day before"?

[fernandapp]

Hello everybody,

I have what seems to be easy but I can’t get any idea left of what to do.

I have a table that a bank publishes with the columns of day and exchange rate (they only publish data for the days that are not weekends or holidays). To calculate the total, I should multiply the sale of the day by the imediate last exchange rate published.

Here’s na example:

A sale made in 02/06/2022 (Reporte CFE 06.22.Fecha) should be multiplied by the rate of "Venta" of 01/02/2022 in "Fecha"). The thing is that I dont have exchange rate for all days, so if I wanna calculate a sale made on a monday, I should use the one published on friday (I will not have any data for the weekend).

Obs: the tables are joined just beacause I thought it would be easier.

Any thoughts?

Thanks in advance!

[ValtteriN · Accepted solution]

Hi,

Here is one way to do this:

Example data:

Data model:

Dax example:

Earlier value =

var _sdate = MAX('Calendar'[Date])

 var _ldate = CALCULATE(MAX(GetEarlier[Column1]),ALL('Calendar'),GetEarlier[Column1]<_sdate)

 return

CALCULATE(MAX(GetEarlier[Column2]),ALL('Calendar'),GetEarlier[Column1]=_ldate)

End result:

By using this kind of logic you should be able to construct the measure you need.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[fernandapp]

Hi, just as I needed!

Thank you!