DAX calculated column using different FX rates based on type and date

[Mattm1989]

Hi all,

I'm struggling for a DAX function/formula with or without use of variables that allows me to select different FX rates based on 1 criteria (Balance Sheet or P&L) ((this is the easy bit which I can do)), but then be able to have a dynamic table for the Balance Sheet rate to change dependant on the reporting date selected.

An example is below:

So basically I'd like another column on the fact table with the rate to be used where:
If P&L, then it would be the corresponding month currency and Average rate regardless of reporting month...
AND then
If BS, the rate would correspond with the currency and use the spot rate, but be dynamic and change based on the reporting date selected, overriding the date in the pFACT table.
I would then need to be able to show the full data set simultaneously, by month, using the reporting month as my columns in a visual.

I'll create some screenshots of my desired output and post them here later today.

If anybody can help, I'd really appreciate it!

(Im only just starting to delve into calculated tables/columns and use variables and parameters so apologies if there is a fairly easy solution for this!

[v-jtian-msft · Accepted solution]

Hi,@Mattm1989 
I am glad to help you.
 I tested it based on the data you provided. You can refer to it
Here is the DAX code.

Feb = 
VAR _table = SUMMARIZE(ALL('Currency_Rates'),'Currency_Rates'[AVE Rate P&L],'Currency_Rates'[Currency],'Currency_Rates'[Date],Currency_Rates[Spot Rate BS])
VAR _Currency= 'pFACT'[Currency]
VAR _table2 = FILTER(_table,'Currency_Rates'[Currency] = _Currency)
VAR _PL = 'pFACT'[P&L/BS]
VAR _month = MONTH('pFACT'[EOMonth])
VAR _table3 = ADDCOLUMNS(_table2,"month",MONTH('Currency_Rates'[Date]))
VAR _table4 = FILTER(_table3,[month] =2)
RETURN SWITCH(TRUE(),
    _PL="P&L",AVERAGEX(_table2,'Currency_Rates'[AVE Rate P&L]),
    _PL= "BS",SUMX(_table4,'Currency_Rates'[Spot Rate BS])
)

Jan = 
VAR _table = SUMMARIZE(ALL('Currency_Rates'),'Currency_Rates'[AVE Rate P&L],'Currency_Rates'[Currency],'Currency_Rates'[Date],Currency_Rates[Spot Rate BS])
VAR _Currency= 'pFACT'[Currency]
VAR _table2 = FILTER(_table,'Currency_Rates'[Currency] = _Currency)
VAR _PL = 'pFACT'[P&L/BS]
VAR _month = MONTH('pFACT'[EOMonth])
VAR _table3 = ADDCOLUMNS(_table2,"month",MONTH('Currency_Rates'[Date]))
VAR _table4 = FILTER(_table3,[month] =1)
RETURN SWITCH(TRUE(),
    _PL="P&L",AVERAGEX(_table2,'Currency_Rates'[AVE Rate P&L]),
    _PL= "BS",SUMX(_table4,'Currency_Rates'[Spot Rate BS])
)

Mar = 
VAR _table = SUMMARIZE(ALL('Currency_Rates'),'Currency_Rates'[AVE Rate P&L],'Currency_Rates'[Currency],'Currency_Rates'[Date],Currency_Rates[Spot Rate BS])
VAR _Currency= 'pFACT'[Currency]
VAR _table2 = FILTER(_table,'Currency_Rates'[Currency] = _Currency)
VAR _PL = 'pFACT'[P&L/BS]
VAR _month = MONTH('pFACT'[EOMonth])
VAR _table3 = ADDCOLUMNS(_table2,"month",MONTH('Currency_Rates'[Date]))
VAR _table4 = FILTER(_table3,[month] =3)
RETURN SWITCH(TRUE(),
    _PL="P&L",AVERAGEX(_table2,'Currency_Rates'[AVE Rate P&L]),
    _PL= "BS",SUMX(_table4,'Currency_Rates'[Spot Rate BS])
)

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[Mattm1989]

Hi Carson

Apologies for my delayed response, I have been on Holiday!.
The above solution is very close to what my required output is to be. However after re-reading my initial post, I don't think I explained the requirement very well.
Below is looking at a single currency. Any of the P&L rates should return the rate that was set for that specific month. The Blance sheet rate should return the latest month rate in that YTD period, but apply it to all previous Balance Sheet transactions in previous months that make up that YTD accumulation.

Hopefully the below helps clarify?

I would be greatful if you could revisit this and help me get the desired output?

Regards

Matt

[Mattm1989]

Here is my desired output, but where the "reporting date" columns on the fact table are actually 1 dynamic calculated column based on which reporting date I am looking at (Probably to be used with a slicer if possible!)