LOOKUP help

[carlosflores]

Greetings Community, first post here,

I have a table like this:

 

So I'm creating a KPI showing the gross marging and I wish to compare it with the same margin of previous period.

For instance, if I'm selecting april as the month in the KPI, there should be a green arrow because the GROSS MARGIN of april is higher than the GROSS MARGIN of march.

I tried using the LOOKUP function, like this:

before = LOOKUPVALUE('Report Italia'[gross margin]; 'Report Italia'[index]; 'Report Italia'[index]-1).

But it doesn't seem to work. The following message appears: The value for 'index' cannot be determined. Either 'index' doesn't exist, or there is no current row for a column named 'index'.

I also tried it liek this:

before = LOOKUPVALUE('Report Italia'[gross margin]; 'Report Italia'[index]; [index]-1). But it doesn't work either

I would really appreciate help here.

Thanks in advance

[Ashish_Mathur]

Hi,

You may download the PBI file from here.

Hope this helps.

[edhans · Accepted solution]

You need to use LOOKUPVALUE in a calculated column. I just created your same data set and it worked fine with this formula in a calculated column called "Prev Gross Margin"

LOOKUPVALUE('Report Italia'[Gross Margin],'Report Italia'[Index],'Report Italia'[Index] - 1)

See this file for an example.

All of that said, the correct way to do this would be to have valid dates in your model, then a date table. Then create a measure that would calculate the margin of the previous period using something along the lines of 

Measure = 
CALCULATE(
    [Total Margin],
    DATEADD([Dates],-1,MONTH)
)

That would ensure it always works as long as you have dates and don't need to worry about an index.

[carlosflores]

@edhans thank you so much. 

LOOKUPVALUE('Report Italia'[Gross Margin],'Report Italia'[Index],'Report Italia'[Index] - 1)

That worked!

I'm trying to figure out how the "corrrect" way to do it works. So, if I had a date table linked to that data table I wouldn't need to use the LOOKUP function ?

Thanks again, really appreciate it.

[edhans]

Great @carlosflores!

You can read about how to create date tables here and learn about date and time intelligence here. For a one off report like you've done it isn't a huge deal, but if you learn about why date tables are so important in Power BI and how to use the time intelligence functions, things like you asked in this thread become very easy and flexible with different time periods. For example, your table is easy to do with a month lookback on the margin. Now do quarter and year! Very difficult. You'd need a new index column. With date intelligence, the formula I posted that had 

DATEADD([Dates],-1,MONTH)

could simply be changed to YEAR or QUARTER and it would work.