Forum Discussion

rve's avatar
rve
New Member
10 years ago
Solved

Previous Year Price Comparison Without Summing

Hi All,   I have a table that has three columns: 1. Region 2. Date  3. Price   I am looking to create a new table with the previous year's price on it so I can do a YoY change.  How do I do th...
  • rve's avatar
    10 years ago

    I have a table with three columns.  Price, date and region.  I want to do a year over year comparison on price.  I don't want to sum the price, but just compare.

     

    How do I create a new column that takes the previos years price and compares it to this year for each region?

     

    I tried:

    PY Ibase = if (YEAR(InstallBase[Date])=2014, 0, Calculate (SUM(InstallBase[Ibase]), DATEADD('Date'[Date], -1, YEAR), InstallBase[GeoID]=4))  

  • v-ljerr-msft's avatar
    10 years ago

    rve

     

    According to your description, the following formulas should meet your requirement.

    PY_Price = 
    CALCULATE (
        SUM ( MyTestTable[Price] ),
        SAMEPERIODLASTYEAR ( MyTestTable[Date] ),
        ALLEXCEPT ( MyTestTable, MyTestTable[Region] )
    )

    Or

    PY_Price_2 = 
    CALCULATE (
        SUM ( MyTestTable[Price] ),
        DATEADD( MyTestTable[Date], -1, YEAR),
        ALLEXCEPT ( MyTestTable, MyTestTable[Region] )
    )

    The following sample is for your reference:

    Assume we have a table called MyTestTable with 3 columns(Region, Date, Price).

    Then we can use the formula I mentioned above to create a measure, and use the measure to show YOY Price change with filter of Date and Region in the report.

     

     

    Regards