Apply last exchange rate in a dynamically selected period across running total to date and sum

[Dangermouse]

Hi all.

New to posting so apologies if I've done something incorrect.

I have exactly the same issue as @Morton041740 as shown in this post https://community.powerbi.com/t5/Desktop/Apply-last-exchange-rate-in-a-dynamically-selected-period-a...and have used the solution - which seems to work fine when the currency is shown however the grand total is using the maximum exchange rate (irrespective of currency)

Let's say I have a sales table with transaction dates, currency and amounts in that currency, e.g.

Date	Ccy	Amount
03/01/2019	CHF	1000
11/03/2019	CHF	500
07/01/2019	EUR	250
07/02/2019	EUR	750
30/01/2019	GBP	1000
20/02/2019	GBP	300

I have a month end FX table converting to USD, thus (note, figures are made up!)

Mth	Ccy	FX to USD
Jan-19	CHF	0.9411
Feb-19	CHF	0.9751
Mar-19	CHF	0.9157
Jan-19	GBP	0.7072
Feb-19	GBP	0.7335
Mar-19	GBP	0.7408
Jan-19	EUR	0.8518
Feb-19	EUR	0.8652
Mar-19	EUR	0.8086

My model relates the month/year of the transaction to the monthly FX rate. I have a date table linked to the sales table.

I want the running total of the sales as at the month end, but revalued in USD at the current FX rate. So even where there has been no transaction in the month, the USD figure as at the end of March will be different to the one in February (say). This my total USD figure for March month end should be 

	running total	Latest FX (March)	USD
GBP	1300	0.7408	1,754.86
EUR	1000	0.8086	1,236.71
CHF	1500	0.9157	1,638.09
TOTAL USD			4,629.66

(In real life I'm trying to consolidate different currency bank transactions into one "closing balance" figure as at month end if this example is hard to imagine)

Have spent lots of hours on this, and I can feel I'm close - but the solution is eluding me

Thanks in advance 

[V-lianl-msft · Accepted solution]

Hi @Dangermouse ,

You can refer to this thread:https://community.powerbi.com/t5/Report-Server/Dynamic-Exchange-Rate-architecture-and-DAX-calculatio... 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[V-lianl-msft · Accepted solution]

Hi @Dangermouse ,

You can refer to this thread:https://community.powerbi.com/t5/Report-Server/Dynamic-Exchange-Rate-architecture-and-DAX-calculatio... 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dangermouse · Accepted solution]

I knew I was close! I needed to change the model. 

I had this model

but the link you provided suggested this model

Once I had that, I built the following components 

FX Latest = 
VAR NormalFX =
    CALCULATE ( MAX ( tblFX[FX] ), RELATEDTABLE ( 'DateTable' ) )
RETURN
    CALCULATE (
        NormalFX,
        FILTER ( ALL ( FXDate ), FXDate[Mth] <= MAX ( DateTable[Date] ) )
    )

OCC Running Total = 
CALCULATE (
    SUM ( tblSales[Amount] ),
    FILTER ( ALL ( DateTable[Date] ), DateTable[Date] <= MAX ( DateTable[Date] ) )
)

USD Running Total Now =
SUMX ( 'Currency', DIVIDE ( [OCC Running Total], [FX Latest] ) )

and it works

Thanks @V-lianl-msft !