Sum of sales with different currency rate for each month

[Jackie003]

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 

[mark_endicott · Accepted solution]

@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?

[mark_endicott · Accepted solution]

@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?

[mark_endicott]

@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?

[some_bih]

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

[Jackie003]

In Power Bi.

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

[some_bih · Accepted solution]

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