Calculate local currency value with Closing currency rate in Chart

[Anonymous]

Dear All,
Tried many option to find a solution for below request , belive some one can help me on this .
Fact Data table - Sales_ : Sales Value per month for two years
Currence Rate Table : Currency Conversion rate per month (only 1 rate per month )

Need to calculate the closing rate for the selected month , that is if I select July 2023 , all months (inlcuding past year ) sales values will multiplied with currency rate of July 2023 and show it separatly for each month as we need to choe this in a line chart by month

Curr_Rate Table:

Tried this calculated column and "Closingbalance" Dax  , dynmaic table but nothing worked 

ClosingRate =

VAR SelectedMonth = [Selected_Date]  

RETURN

CALCULATE(

    MAX(Curr_Rate[Curr_Rate]),

    FILTER(

        Curr_Rate,

        Curr_Rate[Periods] = SelectedMonth

    )

)

Measure : 

Closing = CLOSINGBALANCEYEAR(MAX(Curr_Rate[Curr_Rate]),Calender[Date])  => getting values null.

Visual :

Expected result for "Closing Rate" is "1.10531" for all months (2023 & 2022) 

Thanks in advance 

[Anonymous]

Thank you so much  Mr. Ashish, tried your measure , but getting blank . And when I dragged the measure to table visual all values disappeared from table visual . it is same issue with 'ClosingBalance" measure.For reference attched PBIX file.https://we.tl/t-puEIOOXH06 

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached the PBI file.

Hope this helps.

[Anonymous]

Thank you so much  Mr. Ashish for your support , really it works 🕺...this solution solved my problem 

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Does this measure work?

Measure = CALCULATE(MAX(Curr_Rate[Curr_Rate]),datesbetween(calendar[date],max(calendar[date]),max(calendar[date])))

The slicer should be built from the Calendar Table.

Hope this helps.