Previous Year Price Comparison Without Summing

[rve]

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 this when I need to filter on the previous year and the specific region?

I tried this and it didnt work

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

Thanks

[v-ljerr-msft · Accepted solution]

@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

[v-ljerr-msft · Accepted solution]

@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

[rve]

This worked!

[rve · Accepted solution]

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