cancel
Showing results for
Did you mean:
New Member

## DAX for "last day before"?

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?

1 ACCEPTED SOLUTION
Super User

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!

Proud to be a Super User!

2 REPLIES 2
Super User

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!

Proud to be a Super User!

New Member

Hi, just as I needed!

Thank you!

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors