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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
7LeavesJustine
Regular Visitor

Tracking Product Price Change and list Top Products with the Highest Change and Spend

Hello everyone!

 

I have made this subset of data from a larger set of data.

 

Business Case: I want to track which products have the highest increase in terms of cost so that I can ask my distributor and negotiate with them. This is also a way to know where my money goes and if it is spent effectively.

 

So my main goal for this dashboard is to:

 

  • Track the price changes over time by creating another column to label Increase, Decrease, No Change.
  • Track how much has the price changed over time by creating another column for the percentage and cost of how much has increased, decreased, and 0% for no change.
  • I also want to list down the top 10 that have the highest price change over a certain period of time.
  • Also, if it's possible to know how much has that price has stayed over time.

What I have right now is a slicer with a start date and end date. The data changes daily. So the computation for the Top would take the earliest date and end date of my slicer.

 

Hope this makes sense.

 

About the data
DC (Text) : refers to the distributor
Delivery Date (Date) : the date the product was delivered 

Product Description (Date) : Name of the product

Standard Pack Size (Text): Unit of measure of the product

Cost Per Case (Decimal): Cost of the product

 

DCDelivery DateProduct DescriptionStandard Pack Size Cost Per Case 
VitCo01/03/2022Half & Half12 : 32 Oz $                               23.00
VitCo01/03/2022Whipping Heavy Cream 40%6 : 1/2 Gal $                               40.34
VitCo01/03/2022Sugar Brown Light Golden Med 25Lb1 : 25 Lb $                               23.32
VitCo01/03/2022Sugar Granulated 50 Lb.1 : 50 Lb $                               31.57
VitCo01/03/2022Herbal Mint Fresh10 : 1 Lb $                               73.20
VitCo01/03/2022Orange #11 : 15 Lb $                               12.83
VitCo01/03/2022Milk Sweetened Condensed Black & White24 : 14 Oz $                               47.32
VitCo01/03/2022Salt Sea Coarse 6.6 Lb6 : 6.6 Lb $                               63.31
DC01/03/2022Milk Soy Vanilla Organic12 : 32 Oz $                               17.11
DC01/03/2022Filter Coffee 24 x 11 Urn Paper1 : 250 Cs $                               23.94
DC01/03/2022Holder Cup 4 8-32 Oz1 : 300 Ct $                               40.07
DC01/03/2022Cup 12 Oz Clear PP 360Cc U-Rim (Employee)1 : 2000 Pcs $                               67.24
DC01/03/2022Lid Flat Clear 12 Oz U-Rim Cup (Employee)1 : 2000 Ct $                               38.32
DC01/03/2022Grass Jelly Liquid Form Can12 : 27 Oz Can $                               62.83

 

Here's a snapshot of my dashboard:

The new columns will be added to the table below and they are highlighted in red. 

 

7LeavesJustine_0-1671694634436.png

 

 

 

8 REPLIES 8
Anonymous
Not applicable

Hi @7LeavesJustine 

I changed your file and uploaded it here: https://ufile.io/wyulzcg7

It should work now. Added a measure called 'prevprice', which was your issue.

 

Hope it all works now!

Thank you for fixing the file @Anonymous. As I was trying to tweak with the file, it seems the data is not correct. There should be values for the Price Change ($) and Price Change (%) for the specific date right?

 

For example:

 

Under the following values:

 

Price change for January 10 should be $32.18 - $23.71 = $8.47 so on and so forth. 

 

7LeavesJustine_1-1672128783223.png

 

 

 

 

Attaching the file again as I've missed it. 

 

File: https://drive.google.com/file/d/135GJ9O4jhVUOuXFrgEwEmzVOEgFiQ8HS/view?usp=sharing

Anonymous
Not applicable

Hi @7LeavesJustine ,

Hope this helps.
https://ufile.io/g1ffwlib

 

Anonymous
Not applicable

hi @7LeavesJustine ,

I tried to recreate what you want from your dummy data. Added another month to be able to compare numbers. 
I created on calculated column to get the previous value of the product.

Previous Cost per Case = 
VAR PreviousRow =
    TOPN (
        1,
        FILTER (
            Sheet1,
            Sheet1[Delivery Date] < EARLIER ( Sheet1[Delivery Date] )
                && Sheet1[Product Description] = EARLIER ( Sheet1[Product Description] )
        ),
        Sheet1[Delivery Date], DESC
    )
VAR PreviousValue =
    MINX ( PreviousRow, [ Cost Per Case ] )
RETURN
PreviousValue

 Then you can create your measures by comparing the current price to this column. 

Price Change ($) = 
VAR current_price = CALCULATE([Cost Per Case], MAX(Ark1[Delivery Date]) = Sheet1[Delivery Date])
VAR prevprice = CALCULATE(SUM(Sheet1[Previous Cost per Case]), MAX(Sheet1[Delivery Date]) = Sheet1[Delivery Date])

Return
current_price - prevprice
Price Change (%) = 
DIVIDE([Price Change ($)], [prevprice])
Change Label = 
IF([Price Change ($)] > 0, "Increase", IF([Price Change ($)] < 0, "Decrease", "No Change"))

forenkl_0-1671700227340.png

As for top 10, I reccomend checking out these articles, which explains this topic very well. Or simply use the filter on the filter pane for top 10 by price or what you want.

https://radacad.com/topn-dax-function-how-it-works-in-power-bi-comparison-against-the-top-group

 

Hope this helped you.

Please mark it as resolved if this is what you're looking for 🙂

Thank you for the quick response @Anonymous . I appreciate the time you've put to discuss the solution.

 

Am I doing it right for this formula?

I put "Sum" beofre the 'Sum Data'[Cost Per Case'] in the VAR current_price as it does not read a column and I'm not sure if I'm getting the right values.

Price Change ($) = 
VAR current_price = CALCULATE(SUM('Sum Data'[Cost Per Case]), MAX('Sum Data'[Delivery Date]) = 'Sum Data'[Delivery Date])
VAR prevprice = CALCULATE(SUM('Sum Data'[Previous Cost per Case]), MAX('Sum Data'[Delivery Date]) = 'Sum Data'[Delivery Date])

Return
current_price - prevprice

 

Anonymous
Not applicable

Hi @7LeavesJustine ,
Sorry about that. I created a measure called 

Cost Per Case = SUM(Sheet1[ Cost Per Case ]), which I'm using. Hope that helps!

Thank you @Anonymous. I am able to Price Change ($) but there is an error with Price Change (%).


Error: The value for 'prevprice' cannot be determined. Either the column doesn't exist, or there is no current row for this column.

Price Change (%) = 
DIVIDE([Price Change ($)], [prevprice])

 

I have also attached my test pbix file for us to seamlessly make changes. Here's the link:
https://drive.google.com/file/d/12nMhyBQGVChgC077T0htzTm2bpbzoZSG/view?usp=sharing

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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