cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Sum of sales with different currency rate for each month

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

2 ACCEPTED SOLUTIONS
Responsive Resident

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

``````    VAR Sales_in_Currency =
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?

Super User

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])

Proud to be a Super User!

5 REPLIES 5
Responsive Resident

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

``````    VAR Sales_in_Currency =
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?

Responsive Resident

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

``````    VAR Sales_in_Currency =
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?

Super User

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

Proud to be a Super User!

Frequent Visitor

In Power Bi.

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

Super User

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])

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors