YTD prior with filter on a related table

[Anonymous]

Dear experts,

We have several currencies in our company that should be exchanged into one company currency. We differ 2 kind of exchanges (Historical and Standard).

If we want to calculate the sales in company currency at Feb 2019 with Historical Exchange:

2018 Sales= Total sales 2018* Exchange rate Feb 2018

2019 Sales = Total sales 2019* Exchange rate Feb 2019

If we want to calculate the sales in company currency at Feb 2019 with Standard Exchange:

2018 Sales= Total sales 2018* Exchange rate Feb 2019

2019 Sales= Total sales 2019* Exchange rate Feb 2019

I created two exchange rate table which are filtered by selection of month and year. The Historical did I connect with a key "Currency and Year" and the Standard with "Currency".

This works just fine until I try to use timeintelligence functions as YTD.

The Measure for the Historical Exchange has the following DAX

"Relatedtable EUR (Hist) =

SUMX (

    FILTER (

        RELATEDTABLE ( ( 'Currency (Hist)' ) );

        'Currency (Hist)'[MonthKey] = 'Selection Titel'[Allselected Month]

    );

    Sales[Sales Local Currency] * 'Currency (Hist)'[Value]

)"

The YTD the following

Relatedtable EUR YTD (Hist) = TOTALYTD([Relatedtable EUR (Hist)];'Date'[Date])

What do I miss?

Thank you and best regards

[v-yuta-msft]

@Anonymous ,

Suppose common column is Date between Relatedtable EUR and Date, you may modify measure2 like pattern below:

Relatedtable EUR YTD (Hist) = TOTALYTD([Relatedtable EUR (Hist)];'Relatedtable 
 EUR'[Date])

Community Support Team _ Jimmy Tao

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

[Anonymous]

Dear Jimmy,

The date isn't the common key.

In cases of the measure "Relatedtable EUR (Hist)" I filter the table Currency (Hist) which is related with a Key (FXKey-Hist) to the table Sales by the selected month. The key does merge the monthkey and the currency.

In cases of the measure "Relatedtable EUR (Standard) I filter the table Currency (Standard) which is merged with the Key  "Currency" by selected year.

For any reason the YTD of the "Relatedtable EUR Hist" is calculated correctly but not the "Related EUR (Standard).

The "Relatedtable EUR (Standard) has the following DAX:

Relatedtable EUR (Einheit) =

SUMX (

    FILTER (

        FILTER (

            RELATEDTABLE ( 'Currency (Standard)' );

            'Currency (Standard)'[MonthKey] = 'Selection Titel'[Allselected Month]

        );

        'Currency (Standard)'[Year] = 'Selection Titel'[Allselected Year]

    );

    Sales[Sales Local Currency] * 'Currency (Standard)'[Value]

)

I had a simple PBI File to share 😉

Thank you and best regards,

Ilay

[Anonymous · Accepted solution]

I found out for myself and it had nothing to do with the DAX formula. I was referring to the date in the data table. But as you can see, the relationship is not that way. I changed the data reference to the date key in the fact table and now it works.

Thanks anyway