need help with dax

[Truelearner]

I have the table below where in i have original currency and transaction currency along with effective date and exchnage rate , 

i have this requirement where in which i want to create a table with with highest date when i have two or more exchange rates within same month and i want to have a column in which i would like to have the previous month exchnage rate if the current exchnage rate is not available.

@mgwena @cham @amitchandak @Greg_Deckler @Mariusz 

Input table :

Original curr	Transaction Curr	Effective Date	Exchangerate
USD	INR	02-01-2020	71
USD	INR	18-01-2020	72.05
USD	EUR	07-01-2020	14
USD	INR	04-02-2020	71.5
USD	CAD	07-02-2020	1.01
USD	INR	03-03-2020	72

Output Table :

Original curr	Transaction Curr	Effective Date	Exchangerate	Previous exchnage rate
USD	INR	18-01-2020	72.05
USD	EUR	07-01-2020	14	72.0
USD	CAD	07-02-2020	1.01
USD	INR	03-03-2020	72	14

[v-gizhi-msft]

Hi,

Create a MonthNo column first:

MonthNo = 'Table'[Effective Date].[MonthNo]

Add an index column in Query Editor.

Then try this measure:

check = IF(MAX('Table'[Effective Date])=MAXX(FILTER(ALL('Table'),'Table'[MonthNo] in FILTERS('Table'[MonthNo])),'Table'[Effective Date]),1,0)

Apply it to original table visual by setting check=1, it shows:

Then try this measure:

Previous exchnage rate = CALCULATE(MAX('Table'[Exchangerate]),FILTER(ALLSELECTED('Table'),'Table'[MonthNo]=MAX('Table'[MonthNo])-1))

The result shows:

Tips: Based on your logic, i think the expected output is like the above not the screenshot you posted.

        Please check it.

        If i have understood your requirement, please for free to let me know.

        Expect your reply!

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[amitchandak · Accepted solution]

Your result does seem in line with the description.

Please find solution at https://www.dropbox.com/s/ow37w9hj8aro25z/LastMonthExchnageRate.pbix?dl=0

Use Max month as filter

[Cristian_Angyal]

Hello @Truelearner ,

Check-out this great article from SQLBI: https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/

If you liked my solution please give it a thumbs up 👍. 

If I did answer your question please mark my post as a solution ✅.

Thank you !

Cristian