Calculate Sales Like for Like Previous Year

Hi,

I played a little bit with DNA Enterprise data from one of the courses and then I thought about once scenario.

I would like to calculate last year’s Sales but like for like.

What do I mean by that is, for example, I deleted some data for Product 14 & 15 for the sales as we are no longer have the product. Therefore I would like to compare/ see last year’s sales till the product was available till the last sale of the product. I did manage to work our the formula but in a more manual way.

Is there any chance to create a dynamic formula base on the data filter selected?

Last sale date = Lastonblank formula. Other formulas are self-explained.

I will appreciate your help.

Please find attached file.

Sales like for like if sold product.pbix (349.9 KB)

Hi @Matty,

I’m not sure if I get your question correctly but I’ve created this measure:

Sales exluding Product 14 5 =
VAR _product = SELECTEDVALUE(Sales[Product Description Index])
VAR _qtdeMonthYear = CALCULATE(DISTINCTCOUNT(Data[MonthName]), FILTER(Sales, Sales[Product Description Index]))
RETURN IF(_qtdeMonthYear = 12, [Sales], BLANK())

It calculates the number of months in sales table for each product and check if it has sales for every month in the selected year.

Also, you can use this one for a dynamic period:

Sales exluding Product 14 5 =
VAR _dtStart = MINX(Data, Data[Date])
VAR _dtEnd = MAXX(Data, Data[Date])
VAR _product = SELECTEDVALUE(Sales[Product Description Index])
VAR _qtdeMonthYear = CALCULATE(DISTINCTCOUNT(Data[MonthName]), FILTER(Sales, Sales[Product Description Index]))
RETURN IF((DATEDIFF(_dtStart, _dtEnd, MONTH) + 1) = _qtdeMonthYear, [Sales], BLANK())

It compares the number of selected months in the slicer with the number of months in sales tables for each product.

HI @ricardocamargos88 but this is incorrect.

I still want the results of Product 14 and 15 but only to the point of their last sale date.

Last Sale for Product 14 is 9th May 2016

Last Sale for Product 15 is 30 June 2016

If you would see my formula LY Sales Like for Like is correct result but I would like to create dynamic formula.

I would like to get result for last year sales for Product 14 only up to 9th May 2015 and for Product 15 only till 30 June 16 .

If we select on the slicer dates from 01/01/2016 to 25/05/2016

Then I would like to have LY Sales up to 25 for Products 1-13 and Product 15 , whereas Product 14 only till 9th of May.

But if we select date slicer from 01/01/2016 to 25/07/2016

Then I would like to see LY Sales for Product 1-13 whereas Product 14 up to 9th May 2015 and Product 15 up to 30 June.

Hi @Matty,

I think you need a new column in the Product table, to check if the product is active or not. That’s is necessary because if you calculate the last date for each product and go back 1 year you are gonna miss some values like:

Capture

See that Product 1 in 2016 had sales on 30/12/2016 and in 2015 on 31/12/2015…

I’ve created this column:

and this measure:

LY Sales Till Last Sales Date =
VAR _tb = SUMMARIZE(Products, Products[Product Name], “Total”,
VAR _dtMaxLY = MINX(DATESINPERIOD(Data[Date], CALCULATE(MAX(Sales[OrderDate])), -1, YEAR), Data[Date]) - 1
VAR _active = SELECTEDVALUE(Products[Active])
VAR _LY = MINX(SAMEPERIODLASTYEAR(Data[Date]), Data[Date])
RETURN
IF(
_active = 1,
CALCULATE([Sales], SAMEPERIODLASTYEAR(Data[Date])),
CALCULATE([Sales], FILTER(ALL(Data[Date]), Data[Date] >= _LY && Data[Date] <= _dtMaxLY))
))
RETURN SUMX(_tb, [Total])

See the values:

Or you can just work with relative dates, like going back 1 year (I believe it’s easier) and calculating the last sales date for each product.

Also I checked the measure “2015 Sales exluding Product 14 &15”,
it was filtering dates starting from DATE(2015,7,31), I changed it to DATE(2015,7,1).

Thanks Ricardo I will use abit of your formula as I believe its possible to make it dynamic instead of do it as I did it :slight_smile:

Thank you :slight_smile:

Hi Ricardo,

I did some change in your formula.

Dynamic Slaes =
VAR _dtMaxLY = MINX(DATESINPERIOD(Data[Date], CALCULATE(MAX(Sales[OrderDate])), -1, YEAR), Data[Date]) - 1
VAR _LY = MINX(SAMEPERIODLASTYEAR(Data[Date]), Data[Date])
VAR SALESLY = CALCULATE([Sales],SAMEPERIODLASTYEAR(Data[Date]),Products[Product Name] <> “Product 14” && Products[Product Name] <> “Product 15”)
RETURN

CALCULATE([Sales], FILTER(ALL(Data[Date]), Data[Date] >= _LY && Data[Date] <= _dtMaxLY),
Products[Product Name] = “Product 14” || Products[Product Name] = “Product 15”) + SALESLY

Gives me the exactly result like for like :slight_smile: Thanks for your help.