Forum Discussion
Previous Year Price Comparison Without Summing
- 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))
- 10 years ago
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
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
- rve10 years agoNew Member
This worked!