cancel
Showing results for
Did you mean: New Member

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

2 ACCEPTED SOLUTIONS  Microsoft

@rve

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

Or

```PY_Price_2 =
CALCULATE (
SUM ( MyTestTable[Price] ),
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 New Member

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

3 REPLIES 3  Microsoft

@rve

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

Or

```PY_Price_2 =
CALCULATE (
SUM ( MyTestTable[Price] ),
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 New Member

This worked! New Member

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)) Announcements #### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022. #### Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps #### Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer. Top Solution Authors
Top Kudoed Authors
Users online (1,660)