topic Re: Sum of sales with different currency rate for each month in DAX Commands and Tips

Sum of sales with different currency rate for each month

Jackie003 — Fri, 24 May 2024 10:16:41 GMT

Hello, I would like to ask you for help. I need to calculate Total sales with PLN currency and EUR. I need to calculate in dax Total Sales with EUR currency (every month has different rate). Of course between these tables is relationship - many to 1

Re: Sum of sales with different currency rate for each month

some_bih — Fri, 24 May 2024 10:43:28 GMT

Hi Jackie003 you need solution in Excel with DAX or in Power BI?

Re: Sum of sales with different currency rate for each month

Jackie003 — Fri, 24 May 2024 10:48:32 GMT

In Power Bi.

What's more, every month new currency rate is updated 🙂

Re: Sum of sales with different currency rate for each month

mark_endicott — Fri, 24 May 2024 10:56:39 GMT

Jackie003

I think the DAX below should work, so long as your relationship is set up as I anticipate it is.

VAR Sales_in_Currency = ADDCOLUMNS ( SUMMARIZE ( 'PLN Table', 'PLN Table'[Date] ), "Amt_In_Currency", SUM(Sales PLN), "Rate", CALCULATE ( SELECTEDVALUE ( 'EUR Table'[EUR] ) ) ) VAR calc = SUMX ( Sales_in_Currency, [Amt_In_Currency] * [Rate] ) RETURN calc )

It's not clear what you tables are named, so I have done my best to make this easy to see.

If this works, please could you mark this as the solution?

Re: Sum of sales with different currency rate for each month

mark_endicott — Fri, 24 May 2024 10:57:10 GMT

Jackie003

I think the DAX below should work, so long as your relationship is set up as I anticipate it is.

It's not clear what you tables are named, so I have done my best to make this easy to see.

If this works, please could you mark this as the solution?

Re: Sum of sales with different currency rate for each month

some_bih — Fri, 24 May 2024 12:17:25 GMT

Hi Jackie003 one possible solution:

create 2 calculated columns in Sales table (order is important) and two measures (starting with M)

EUR_rate = CALCULATE(

MAX(EUR[EUR]),

FILTER(EUR,EUR[Date]>=Sales[Date] && EUR[Date]<=Sales[Date])

)

Sales_EUR = ROUND(Sales[Sales_PLN]*Sales[EUR_rate],0)

Measures:

M_Sales_PLN = SUM(Sales[Sales_PLN])

M_Sales_EUR = SUM(Sales[Sales_EUR])