Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rve
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
v-ljerr-msft
Employee
Employee

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

py01.PNG

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.

py02.PNG

 

 

Regards

View solution in original post

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

View solution in original post

3 REPLIES 3
v-ljerr-msft
Employee
Employee

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

py01.PNG

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.

py02.PNG

 

 

Regards

This worked!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.