Cumulative currency conversion with YTD and Lookupvalue problem

[kip]

Hi

I would need some help with an issue I'm facing and I don't understand.

In my simplified model I have 3 tables (2 sales tables just to demonstrate the issue); Date; exchange rates and sales table.

My target is to calculate cumulatively local amounts by month and then use month's exchange rate to convert the amount to reporting currency. And then subtract previous month's cumulative amount of latest amount to have isolated monthly value in reporting currency.

I get correct result in PBI Test Sales 2 table with its measures RateYTD 2. That seems to be because that table has one-to-one relationship with date table. If I have many-to-one relationship with date table (PBI Test Sales) then same measure (RateYTD) is not working in the way I like it to work. It is just multiplying isolated months with the exchange rate. Unfortunately I need to have many-to-one relationship there.

My measures are same for both tables:

SalesMeasure2 = SUM('PBI Test Sales 2'[Sales])

Sales YTD 2 = CALCULATE([SalesMeasure2]; DATESYTD('Table'[Date]))

Rate 2 = SUMX('PBI Test Sales 2'; [SalesMeasure2]*
    LOOKUPVALUE('PBI Test rate'[Rate];'PBI Test rate'[YYMM]; 'PBI Test Sales 2'[YYMM]))

RateYTD 2 = SUMX('PBI Test Sales 2'; [Sales YTD 2] * LOOKUPVALUE('PBI Test rate'[Rate]; 'PBI Test rate'[YYMM];'PBI Test Sales 2'[YYMM]))

What can be the case here? Why is it behaving like that?

Link to PBI example I used:

https://www.dropbox.com/s/kxfaq7zhbhg6cvt/Currency%20rate%20test.pbix?dl=0

[v-shex-msft · Accepted solution]

HI @kip,

You can try to use below measure to calculate YTD sales:

Measure =
SUMX (
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'PBI Test Sales' ),
            [Date] <= MAX ( [Date] )
                && YEAR ( [Date] ) = YEAR ( MAX ( [Date] ) )
                && [Version] IN ALLSELECTED ( 'PBI Test Sales'[Version] )
        ),
        [YYMM],
        [Date],
        [Version],
        [Sales],
        "Rate", LOOKUPVALUE (
            'PBI Test rate'[Rate],
            [YYMM], VALUE ( FORMAT ( [Date], "yymm" ) )
        )
    ),
    [Sales] * [Rate]
)

Regards,

Xiaoxin Sheng