Based on selected filter, control currency year in FX conversion

[Martin99]

Hi 

I am struggling with Dax calculation on "Based on selected filter, control currency year in FX conversion" and I hope there might be some clever heads in this forum, that would be able to help me solving this isssue. 

I have setup this measure and marked in red part that does not work, where I need help: 

Issue: 

A Net Sales Test 4 =

- CALCULATE (

[Amount Real LCU Converted],

'Finance Account'[Account Category Name]

IN { "NetSales", "Group License Inc", "Othincome" },

'Currency Select Currency'[Currency Select Currency] = "DKK",

'Currency Cross Rate'[Currency Select Actuality] = "AC",

'Currency Conversion Method'[CurrencyConversionMethodNumber] = 1,

'Currency Cross Rate'[Currency Select Currency Type] = "Average Month",

If( SELECTEDVALUE ('Time Intelligence'[Time Calc] ) = "YTD LY",

'Currency Cross Rate'[Currency Select Year] = values ('Calendar'[Year] ),

'Currency Cross Rate'[Currency Select Year] = values ('Calendar'[PreviousYear] ))

)

If I replace red markings with either 

1) 'Currency Cross Rate'[Currency Select Year] = values ('Calendar'[Year] )

2) 'Currency Cross Rate'[Currency Select Year] = values ('Calendar'[PreviousYear] )

then measure works, but either way data is converted wrongly 

At measure 1) data on 2020 is FX converted with FX rates for 2020 and data on 2019 is FX converted with FX rates for 2019

At measure 2) data on 2020 is FX converted with FX rates for 2019 and data on 2019 is FX converted with FX rates for 2018

What I want is that data on 2020 is FX converted with FX rates for 2019 and data on 2019 is FX converted with FX rates for 2019

Is there someon in this forum that could help me or guide me in the direction of a solution to this problem ? 

Context: 

Measure is to be used in a context, where a Calender date filter YYYY-MM selection control data to be shown for measures on Actuals YTD, Actuals YTD LY and BU YTD, but when comparing YTD data vs YTD LY,

• I need to make sure that YTD data is FX converted at 'Currency Cross Rate'[Currency Select Year] = values ('Calendar'[PreviousYear] )
• I need to make sure that YTD LY data is FX converted at 'Currency Cross Rate'[Currency Select Year] = values ('Calendar'[Year] )

This way I control that both years data are FX converted at same FX rate year. 

Measures on YTD uses 'Time Intelligence'[Time Calc] ) = "YTD"

Measures on YTD LY uses 'Time Intelligence'[Time Calc] ) = "YTD LY"

Error message: 

[v-janeyg-msft · Accepted solution]

hI, @Martin99 

It’s my pleasure to answer for you.

According to your description,Your problem is that you wrote the red part( if function) on the 'filter' of calculate(expression, filter), which is not allowed.

You can modify your mesure like this:

A Net Sales Test 4 =
IF (
    SELECTEDVALUE ( 'Time Intelligence'[Time Calc] ) = "YTD LY",
    - CALCULATE (
        [Amount Real LCU Converted],
        'Finance Account'[Account Category Name]
            IN { "NetSales", "Group License Inc", "Othincome" },
        'Currency Select Currency'[Currency Select Currency] = "DKK",
        'Currency Cross Rate'[Currency Select Actuality] = "AC",
        'Currency Conversion Method'[CurrencyConversionMethodNumber] = 1,
        'Currency Cross Rate'[Currency Select Currency Type] = "Average Month",
        'Currency Cross Rate'[Currency Select Year] = VALUES ( 'Calendar'[Year] )
    ),
    - CALCULATE (
        [Amount Real LCU Converted],
        'Finance Account'[Account Category Name]
            IN { "NetSales", "Group License Inc", "Othincome" },
        'Currency Select Currency'[Currency Select Currency] = "DKK",
        'Currency Cross Rate'[Currency Select Actuality] = "AC",
        'Currency Conversion Method'[CurrencyConversionMethodNumber] = 1,
        'Currency Cross Rate'[Currency Select Currency Type] = "Average Month",
        'Currency Cross Rate'[Currency Select Year]
            = VALUES ( 'Calendar'[PreviousYear] )
    )
)

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[CheenuSing]

Hi @Martin99 ,

Can you load data and output expected based on the measure and the slicer selection, and share the oneDrive or googledrive drive link here.  If possible share your pbix also.

Cheers

CheenuSing