cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fernandapp
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:

fernandapp_0-1661274087321.png

 

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!

1 ACCEPTED SOLUTION
ValtteriN
Super User
Super User

Hi,

Here is one way to do this:

Example data:

ValtteriN_0-1661276538735.png

 

Data model:

ValtteriN_1-1661276777484.png

 



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:

ValtteriN_2-1661278486820.png

 


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/





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

Proud to be a Super User!




View solution in original post

2 REPLIES 2
ValtteriN
Super User
Super User

Hi,

Here is one way to do this:

Example data:

ValtteriN_0-1661276538735.png

 

Data model:

ValtteriN_1-1661276777484.png

 



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:

ValtteriN_2-1661278486820.png

 


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/





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

Proud to be a Super User!




Hi, just as I needed!

Thank you!

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

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