Compare if Product Current Row Matches the Same Product Previous Month

[nikkollai]

Hello,

I would really appreciate some help on this one. Let's say we have no dims and facts just a simple extended product table below.

How would you compare if Unit Price matches or not with the price of the same product sold the previous month?



Thank you

ProductId	DateKey	Date	QttySold	UnitPrice
14344	20163700	1/27/2016	134	6.00
16239	20163800	1/28/2016	389	9.00
16239	20163900	1/29/2016	2334	10.00
15233	20164000	1/30/2016	1983	9.00
12657	20164100	1/31/2016	2872	9.00
13972	20162100	2/1/2016	1897	7.00
18123	20162200	2/2/2016	3132	9.00
15795	20162300	2/3/2016	3343	5.00
18643	20162400	2/4/2016	3354	9.00
13294	20162500	2/5/2016	1204	9.00
14806	20162600	2/6/2016	819	7.00
13997	20162700	2/7/2016	2484	9.00
11787	20162800	2/8/2016	2145	10.00
17145	20162900	2/9/2016	2341	4.00
12657	20163000	2/10/2016	2139	6.00
16786	20163100	2/11/2016	2570	10.00
12657	20163200	2/12/2016	3123	6.00
16239	20163300	2/13/2016	1520	5.00
12657	20163400	2/14/2016	1905	4.00
16325	20163500	2/15/2016	1956	4.00
17550	20163600	2/16/2016	1628	7.00

<tbody>
</tbody>

 

[jorismoerings]

Hi,
As there is the possibility of multiple products with different prices sold in the previous month you can check the price from this sale with the prices from previous months by

1. sum the price of the product if the product is similar to sold products in the previous month
2. count the number of transactions if the product is similar to previous sold products in the previous month
3. divide 1 and 2
4. you become the average price per product in the previous month.
5. compare that to the price of this month's sale;
6. if they are similar; prices are the same; if they differ than you've used different prices.

This is one way of doing it:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F
1	ProductId	DateKey	Date	QttySold	UnitPrice	Prev Mth Price
2	14344	20163700	27-1-2016	134	6
3	16239	20163800	28-1-2016	389	5
4	16239	20163900	29-1-2016	2334	5
5	15233	20164000	30-1-2016	1983	9
6	12657	20164100	31-1-2016	2872	9
7	13972	20162100	1-2-2016	1897	7
8	18123	20162200	2-2-2016	3132	9
9	15795	20162300	3-2-2016	3343	5
10	18643	20162400	4-2-2016	3354	9
11	13294	20162500	5-2-2016	1204	9
12	14806	20162600	6-2-2016	819	7
13	13997	20162700	7-2-2016	2484	9
14	11787	20162800	8-2-2016	2145	10
15	17145	20162900	9-2-2016	2341	4
16	12657	20163000	10-2-2016	2139	6	FALSE
17	16786	20163100	11-2-2016	2570	10
18	12657	20163200	12-2-2016	3123	6	FALSE
19	16239	20163300	13-2-2016	1520	5	TRUE
20	12657	20163400	14-2-2016	1905	4	FALSE
21	16325	20163500	15-2-2016	1956	4
22	17550	20163600	16-2-2016	1628	7
Sheet1

Cell Formulas
Range	Formula
F2	=IFERROR(SUMPRODUCT((A2=$A$2:A2)*(MONTH(C2)-1=MONTH($C$2:C2)),($E$2:E2))/SUMPRODUCT((A2=$A$2:A2)*(MONTH(C2)-1=MONTH($C$2:C2)))=E2,"")

NB: i modified your data to make sure there was at least one row which evaluates to true.

 

[Matt Allington]

I assume you are looking for a power pivot solution, not an excel solution. I suggest creating a calendar table with a month ID column. Read about why here Power Pivot Calendar Tables - Excelerator BI

then you can write something like this.

Avg price previous month = calculate(average(data[unit price]), filter(all(calendar),calendar[monthID]=max(calendar[monthID])))

 

[nikkollai]

jorismoerings

You presented a very detailed answer thank you very much. Even though I was hoping for a DAX code it is great to see an excel solution. Serves a reminder once again that excel is indeed a very powerful and flexible tool.

N -

Hi,
As there is the possibility of multiple products with different prices sold in the previous month you can check the price from this sale with the prices from previous months by

1. sum the price of the product if the product is similar to sold products in the previous month
2. count the number of transactions if the product is similar to previous sold products in the previous month
3. divide 1 and 2
4. you become the average price per product in the previous month.
5. compare that to the price of this month's sale;
6. if they are similar; prices are the same; if they differ than you've used different prices.

This is one way of doing it:
Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F
1	ProductId	DateKey	Date	QttySold	UnitPrice	Prev Mth Price
2	14344	20163700	27-1-2016	134	6
3	16239	20163800	28-1-2016	389	5
4	16239	20163900	29-1-2016	2334	5
5	15233	20164000	30-1-2016	1983	9
6	12657	20164100	31-1-2016	2872	9
7	13972	20162100	1-2-2016	1897	7
8	18123	20162200	2-2-2016	3132	9
9	15795	20162300	3-2-2016	3343	5
10	18643	20162400	4-2-2016	3354	9
11	13294	20162500	5-2-2016	1204	9
12	14806	20162600	6-2-2016	819	7
13	13997	20162700	7-2-2016	2484	9
14	11787	20162800	8-2-2016	2145	10
15	17145	20162900	9-2-2016	2341	4
16	12657	20163000	10-2-2016	2139	6	FALSE
17	16786	20163100	11-2-2016	2570	10
18	12657	20163200	12-2-2016	3123	6	FALSE
19	16239	20163300	13-2-2016	1520	5	TRUE
20	12657	20163400	14-2-2016	1905	4	FALSE
21	16325	20163500	15-2-2016	1956	4
22	17550	20163600	16-2-2016	1628	7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F2 =IFERROR(SUMPRODUCT((A2=$A$2:A2)*(MONTH(C2)-1=MONTH($C$2:C2)),($E$2:E2))/SUMPRODUCT((A2=$A$2:A2)*(MONTH(C2)-1=MONTH($C$2:C2)))=E2,"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

NB: i modified your data to make sure there was at least one row which evaluates to true.

 

[nikkollai]

Thank you Matt,

I am not sure If I am on the right track here but I feel like the DAX should look something like this. The issue is to figure out that comparing expression below marked in red.


=CALCULATE(
"EXPRESSION COMPARING CURRENT MONTH UNIT PRICE WITH PREVISOU HERE????",
FILTER(
ALL(DataTable),
DataTable[ProductID] = EARLIER(DataTable[ProductID])
)
)




N-

I assume you are looking for a power pivot solution, not an excel solution. I suggest creating a calendar table with a month ID column. Read about why here Power Pivot Calendar Tables - Excelerator BI

then you can write something like this.

Avg price previous month = calculate(average(data[unit price]), filter(all(calendar),calendar[monthID]=max(calendar[monthID])))

 

[Matt Allington]

Thank you Matt,

I am not sure If I am on the right track here but I feel like the DAX should look something like this. The issue is to figure out that comparing expression below marked in red.

=CALCULATE(
"EXPRESSION COMPARING CURRENT MONTH UNIT PRICE WITH PREVISOU HERE????",
FILTER(
ALL(DataTable),
DataTable[ProductID] = EARLIER(DataTable[ProductID])
 )
)

Sorry for the slow reply. No this is not really the approach. The trick is to break the problem into pieces.

1. Get the value for this period
2. Get the value for the previous period
3. compare 1 to 2 to get the comparison you need.

So the secret sauce is in the FILTER statement. This is a time shifting formula. It goes back in time to fetch the same expression but at a previous point it time (it is a time machine). Does that make sense?