Dealing With Products That Have Changing Prices

Thimios · January 17, 2022, 9:19am

Hi all,

First post for 2022, so you may all have a happy new year. Stay safe!

Our pricelist changes partially throughout the year either due to cost changes or because of promotion activities. I need to:

Compare sales value for chosen date period vs same period last year (filtering by product) based on respective prices.
Calculate the sales value difference (by product) for selected dates (sales quantity for selected dates multiplied by the difference of current unit price minus the previous product sales prices).

Although I created an inactive connection between Sales table and Pricelist & Costing table -this is where the price per product is set- (CostHelper column), I cannot get to calculate the above.

I studied Sam’s video on Dealing With Products That Have Changing Prices but cannot get that working.
Could this be a modelling issue, or can this be solved with DAX?

TestES.pbix (37.6 MB)

ankit · January 21, 2022, 11:38am

Hi @Thimios - PBIX file only contains Model with no visuals. It is not feasible to determine what are you trying and whats the exact issue.

Please share more details like fields being used, the exact issue etc with Screenshots for further help.

Thanks
Ankit J

EnterpriseDNA · January 24, 2022, 12:44am

Hello @Thimios, it’s been a while since we got a response from you.

Just following up the information @ankit requested above so he can help you further.

Please be reminded that In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

deltaselect · January 24, 2022, 8:08am

Hi @Thimios ,

Interesting questions, compare sales values with LY and analysis of sales differences.

Please receive hereby a solution, with the following steps:

Slight adjustment data model
Sales, quantity and average price calculation by creating measures (see group “DS Measures”)
Sales, quantity and average price calculation previous year with help of the “DateAdd”-function and measure branching
Setting up price and quantity variations to analyze the sales variances, based upon Price impact (AvPrice - LY AvPrice) * Qty) and Qty impact ((Qty - LY Qty) * LY AvPrice)
Base setup of an analysis report, which can be used regardless chosen products or time periods.

The solution is an extension of one of the videos of Sam McKay, which clearly explains time comparisons in combination with measure branching, titled "This Year vs Last Year Time Comparisons & Differences In Power BI " , https://www.youtube.com/watch?v=nrjJuNbzKRA

Workout explained:

PBIX with solution attached:
TestES (v2).pbix (37.6 MB)

1. Slight adjustment data model, use “CostHelper” as the key for sales price information.

“Pricelist & Costing” is the base for the sales price, with an unique key “Costhelper”, which combines the product code with a starting date period of the price (in this table multiple-sales prices per product are stored, each with an unique key “Costhelper” (some prices are missing)
“Sales Catalogue” can be used to distinguish Product characteristics in reports
“Date” is used to distinguish different periods and to calculate previous year (and/or other periods)

image764×580 26.8 KB

2. Sales, quantity and average price calculation measures, in group “DS Measures”

 Sales revenue = SUMX( Sales, Sales[ΠΟΣΟΤΗΤΑ QTY] * RELATED('Pricelist & Costing'[Pricelist]))
 Sales Qty = SUM(Sales[ΠΟΣΟΤΗΤΑ QTY])`
 Sales avg price = DIVIDE( [Sales revenue], [Sales Qty],0)  // measure branching

3. Sales, quantity and average price calculation previous year with help of the “DateAdd”-function and measure branching (instead of “YEAR” in DateAdd, other periods can be chosen.)

LY Sales revenue = CALCULATE( [Sales revenue], DATEADD('Date'[Date], -1, YEAR))
LY Sales Qty = CALCULATE( [Sales Qty], DATEADD('Date'[Date], -1, YEAR))
LY Sales avg price = CALCULATE( [Sales avg price], DATEADD('Date'[Date],-1,YEAR))

4. Setting up price and quantity variations to analyze the sales variances, with calculations, based upon Price impact (AvPrice - LY AvPrice) * Qty) and Qty impact ((Qty - LY Qty) * LY AvPrice), all set up with measure branching

Those formulas work for all selected (aggregations of) time periods and products.

Diff Sales Revenue - LY Sales Revenue = [Sales revenue] - [LY Sales revenue]
Diff Sales Qty - LY Sales Qty = [Sales Qty] - [LY Sales Qty]

Qty impact ((Qty - LY Qty) * LY AvPrice) = ([Sales Qty] -[LY Sales Qty]) * [LY Sales avg price]
Price impact (AvPrice -LY AvPrice) * Qty) = ([Sales avg price] - [LY Sales avg price]) *[Sales Qty]

5. Base setup of an analysis report
Can be used for all chosen (groups of) products and time periods. See page “sales analysis”.

This appears to be a nice example of the power of measure branching.

I hope this answers your requirements, if you have further questions or remarks, please let me know.

Kind regards, Jan van der Wind

EnterpriseDNA · January 25, 2022, 12:56am

Thanks for working on a solution on this post @deltaselect.

Amazing job on how you created a step by step process for this inquiry.

Due to inactivity from @Thimios, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.

Thimios · January 26, 2022, 9:32am

Hi @deltaselect @ankit @EnterpriseDNA ,
Pls forgive me for coming back late but weather conditions are causing serious issues here in Greece, setting back all my tasks.
Allow me to review your suggestions and I’ll provide neccessary feedback within the next couple of hours.

Thimios · January 26, 2022, 1:41pm

Thank you @deltaselect for the great solution provided.
For me, the secret was on the model and on calculating correctly the Sales revenue measure. Nice touch though the Qty and Price Impact, these make reporting meaningfull.
I apologize for taking so long to review, I hope I could manage sooner.
Thanks again for such an elaborate walkthrough.

Btw, can you think of a DAX way to filter items for which pricelist has changed in the selected timeframe so I can use that as a page or a visual filter?

deltaselect · January 26, 2022, 3:07pm

Hi @Thimios ,

This sounds like a new request, for on the forum. If you want to post, please make sure all column- headers are in English, and delete not needed columns/tables.

Could you exactly define what means “to filter items for which pricelist has changed”, because almost every product changes in price over time ? Which limited outcome do you want to see, what report do you want to generate, could you please be very specific ? (maybe use product 100 as an example)

P.S. Your car came through the snow ?

Kind regards, Jan van der Wind

Thimios · January 26, 2022, 3:54pm

Hi @deltaselect ,
I’m already a pro cross-country skier now

I added one column on your table (Item), right after Dates so I can get results per product.
Just realized though that filtering the table to show results only if Price impact is other than zero will take me there. No need to complicate things further, right?

I noticed though that Totals for Price Impact does not work. How would you manage that?

deltaselect · January 26, 2022, 10:40pm

Hi @Thimios ,

Next year January I am planning for ice-skating at Kreta

Your question about filtering can be done as follows: (was my initial thought)
METHOD II new
adjusting the measures Net Price impact (also done Net Quantity impact) , extending the formula with a forced total calculation, and using “Blank()”

Be aware that “VALUES(‘Date’[Calendar Month Year]” should be in the formula, similar to (Calendar Month Year) context chosen in the report.
Be also aware to choose “Blank()”, not 0, to get rid of zero values in the month.

Net Price impact = 
       sumx( VALUES('Date'[Calendar Month Year]),
               if([Price impact (AvPrice -LY AvPrice) * Qty)] <> 0 , 
                           [Price impact (AvPrice -LY AvPrice) * Qty)], 
                        BLANK()))

Net Quantity impact = 
       sumx( VALUES('Date'[Calendar Month Year]),
               if([Qty impact ((Qty - LY Qty) * LY AvPrice)] <> 0 , 
                           [Qty impact ((Qty - LY Qty) * LY AvPrice)], 
                        BLANK()))

Having adjusted the measures as above, there seems to occur a weird problem .
The formulas above counting the totals row by row for Net Price Impact and Net Quantity Impact for the months, differs from the totals presented with the original formulas (see two pictures below ).

METHOD I Original formulas
The original two formulas have a different Total calculation method.
Those formulas do give the correct total difference, using the calculations (Av Price - Av Price LY) x Quantity and (Qty - Qty LY) x Price LY for the totals, when taking the Total Quantities and Total Sales and Total Average Price. but individual amounts (per month) do hereby not add up to the total.

Method I not presenting zero values (no price differences or quantity differences), see below adjusted formulas:

Price impact (AvPrice -LY AvPrice) * Qty) = if(([Sales avg price] - [LY Sales avg price]) *[Sales Qty] =0, 
                                                    BLANK(), 
                                                    ([Sales avg price] - [LY Sales avg price]) *[Sales Qty])

Qty impact ((Qty - LY Qty) * LY AvPrice) = IF(([Sales Qty] -[LY Sales Qty]) * [LY Sales avg price] = 0, 
                                      blank(), 
                                      ([Sales Qty] -[LY Sales Qty]) * [LY Sales avg price])

Explanation differences between two methods for total difference:
The two total average prices are based upon Weighted Average (Total Sales divided by Total Quantity), which are correct.
Apparently the calculations for the month, based upon calculations per month and average price per month gives indeed different net price and net quantity impacts, then when calculating those two effects for the whole year at once! (in this case are the total year price and quantity effects bigger then the effects from the twelve individual effects totaled together.)
Good to see that the total difference calculated for both twelve individual months and one year are the same (585,20 - 455,80 = 129,40 and 803,91 - 674,51 = 129,40), see product 100 Expresso below.

Example with 100 Espresso

A clear example of the described effect with product 1006 Mars

I would opt method I, as the totals are always correctly calculated.

PBIX attached:
TestES (v3).pbix (37.6 MB)

Kind regards,
Jan van der Wind

deltaselect · January 28, 2022, 5:49pm

Good evening @Thimios

PBIX with report:
TestES (v5).pbix (37.6 MB)

Last but not least, a possible report of price difference effects per BU:

Kind regards,
Jan van der Wind