Year over Year sales

[Anonymous]

Hello everyone,

I’m working with a data set encompassing product line, global location, sales quarter, and total sales. I’m trying to create a year over year sales comparison by quarter. My data set looks as follows:

Product Line	Global Location	Sales Quarter	Total Sales
Notebook	North America	2017Q2	1000
Notebook	North America	2017Q3	500
Notebook	North America	2017Q4	750
Notebook	North America	2018Q1	850
Notebook	North America	2018Q2	900
Notebook	North America	2018Q3	1025
Notebook	North America	2018Q4	1250

I created a 2nd table to convert the Sales Quarter column (text format) into a date format based on the start date of the quarter (mm/dd/yyyy), then I created a new column “Quarter-Date” to add this new data to the dataset.

I’m now trying to create a column “Prev Yr Total” to create a column from which I can compare the Total Sales to Last Year’s Sales. I’m using the following formula in PowerBI, however I am not getting any results back:

Prev Yr Total = CALCULATE(SUM([Total Sales]),SAMEPERIODLASTYEAR([Quarter-Date]))

I understand that I will not receive a response in every case as the 2016 sales data is not part of the data set. Any idea why I’m not getting any results for the Prev Yr Total formula?

[v-piga-msft]

Hi @Anonymous ,

You could refer to my test below. I have added some data for 2016 so that the result will be much obvious.

You could create a year column with LEFT fucntion like this and change the data type to be whole number.

Year = LEFT('Table'[Sales Quater],4)

Then create a date column with the formula below.

date = DATE('Table'[Year],1,1)

In addtion, you need create a calendar table and create the relationship with the original table.

And then create the measures below.

current_year =
CALCULATE (
    SUM ( 'Table'[Total Sales] ),
    FILTER ( 'Table', YEAR ( 'Table'[date] ) = YEAR ( MAX ( 'calendar'[Date] ) ) )
)
previous_year =
CALCULATE (
    SUM ( 'Table'[Total Sales] ),
    SAMEPERIODLASTYEAR ( 'calendar'[Date] )
)

Here is the output.

For more details, please refer to my attachment.

Best Regards,

Cherry

[Anonymous]

Hello Cherry, 

Thank you for your help with this endevor. In your example, I see that you are performing the Year over Year comparison task I'm looking for, however it appears that you are comparing Total Year over Year. I may not have communicated this correctly, but I am looking to for a Year over Year comparison by quarter, for example 2017Q2 vs 2018Q2 Totals comparison. 

Your assistance is appreciated,

Neel D

[Geradav · Accepted solution]

Hi @Anonymous 

Do you have a date table in your data model? And have you created a relationship between the tables?

Also, there is a new feature in Power BI called "Quick Measure" worth looking at.