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.