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
    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.