Time intelligence fucntion inside Vlookup (need to create the ROW by currency context inside DAX)

mark.lovejoy · April 3, 2019, 12:42pm

Hello,

what I’m trying to achieve is getting last year sales (2018) multiply by actual year Budget rate (2019) so I can compare this to Actual year sales at AY budget (2019).

I m using Vlookup to get retrieve the currency and the exchange rate. With this formula:

Amounttoconvert = SUMX(Entries, Entries[CC amount] *
                    LOOKUPVALUE('Currency rates (BG&FORECAST)'[BG'19 Rates],
                        'Currency rates (BG&FORECAST)'[Currency I have] , Entries[Company currency],
                        'Currency rates (BG&FORECAST)'[Currency I want] , "CHF",
                        'Currency rates (BG&FORECAST)'[DateYear] , FORMAT(Entries[Date], "YYYY")

This works perfectly since I’m multiplying the each amount by the correct rates given by the Lookupvalue function. But when doing this for LY amount the problem is that I do Calculate( sum( CC amount) , sameperiodelastyear (Dates)) and this formula takes away the “row structure” so the formula would just retrieve nothing.

Any idea?

Thanks
Best regards

Mark Lovejoy

Nick_M · April 3, 2019, 2:03pm

@mark.lovejoy
Any chance you can load some sample data?

mark.lovejoy · April 3, 2019, 2:31pm

Sadly its not going to be possible, the data model is to big and to many files are linked to it.

But really I feel like what I m trying to do is simple in theory. In excel basically we would have the amount of 2018 that we decide to mupltiply by the rates of 2019.

So I can get 2018 amounts at 2019 budget rates and 2019 amounts at 2019 rates. In the context of 2019 I need to retrieve sameperiodelastyear to have 2018 amounts (this is simple) and then multiply them by the related currency (EUR/CHF/USD,…) of 2019 rates. This part doesnt work since the sameperiodelastyear formula force me to enter an expression calculate(sum(amount CC) , Sameperiodelastyear(dates)) and this “suppress” the ROW by ROW context. So then I m doing this:

Amounttoconvert = SUMX(Entries, **LYAMOUNT**  *
LOOKUPVALUE(‘Currency rates (BG&amp;FORECAST)’[BG’19 Rates],
‘Currency rates (BG&amp;FORECAST)’[Currency I have] , Entries[Company currency],
‘Currency rates (BG&amp;FORECAST)’[Currency I want] , “CHF”,
‘Currency rates (BG&amp;FORECAST)’[DateYear] , FORMAT(Entries[Date], “YYYY”)

But this LYAMOUNT has no row context so can’t work in Lookupvalue row by row verification.

DOes this helps understand >_<?

Regards

Mark Lovejoy

Nick_M · April 4, 2019, 6:39pm

@mark.lovejoy
Please take a look at the attached. I had to make some assumptions and probably overkill for what you need, but I believe the the pieces are there. Few notes:

Added a date table and marked it as such. Not sure if you mentioned if you had one or not
Created a BudgetRates table at the monthly level
Pulled in some CHF rates. Also used a data set Sam has on here as well. Both files are attached. In PowerQuery there is a parameter to enter in the folder location and then everything will work.

Without actually seeing anything, this is the best I could come up with.

PBIX:
Currency Conversion.pbix (348.8 KB)

Source Files:
US Retail Sites.xlsx (914.5 KB)
us-dollar-swiss-franc-exchange-rate-historical-chart.csv (135.1 KB)