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

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

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

Enterprise%20DNA%20Expert%20-%20Small

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&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”)

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

DOes this helps understand >_<?

Regards

Mark Lovejoy

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

  1. Added a date table and marked it as such. Not sure if you mentioned if you had one or not
  2. Created a BudgetRates table at the monthly level
  3. 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)

Enterprise%20DNA%20Expert%20-%20Small

1 Like