Forum Discussion

carlosflores's avatar
carlosflores
Icon for Helper I rankHelper I
8 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
    8 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
Icon for Community Champion rankCommunity Champion
8 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
Icon for Helper I rankHelper 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.