Forum Discussion

carlosflores's avatar
carlosflores
Helper I
7 years ago
Solved

LOOKUP help

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.   ...
  • edhans's avatar
    edhans
    7 years ago

    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.

edhans's avatar
edhans
Community Champion
7 years ago

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's avatar
carlosflores
Helper I
7 years ago

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's avatar
    edhans
    Community Champion
    7 years ago

    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.