Forum Discussion

Mattm1989's avatar
Mattm1989
Regular Visitor
2 years ago
Solved

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

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 wh...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Mattm1989
Regular Visitor
2 years ago

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