Prince change history chart

[Antonio_Carneir]

Hey all!

I’m currently developing a dashboard in which the user is supposed to be capable of tracking down the price of one or more SKUs over time. For doing this, I was provided with a huge database that contains the following columns:

1. SKU name: Name of all SKUs in the market;

2. Date

3. State: States of the country

4. Price: Contains the price of a certain SKU, in a certain state on a certain date.

This database is updated every day to provide the prices of all the listed SKUs in the market in their respective states. I also imported a calendar table for support.

After some modeling, I’ve managed to obtain this chart right here. There are some relevant details about it that you might want to know:

• The dates range from Jan’20 to Jan’24. However, I’ve managed to filter only the dates on which any SKU has suffered a price change (not necessarily the ones being displayed in the chart).

I would like to make this visual a little bit more “clean”, removing all the irrelevant dates (that being those of which the prices displayed in the chart were not altered). Do you have any suggestions?

Regards, Antônio

[lbendlin]

There are a lot of things you need to consider here, not the least of them being Nyquist/Shannon.  You could for example aggregate your date range to month, quarter or year level, but that would immediately mean that you lose visibility to the fact that there may have been multiple price changes in your chosen interval. You could consider a start/min/max/end value display for each interval and product, for example.

What is the business problem you are trying to solve? What insights are you expecting from the report?

[Antonio_Carneir]

The purpose of this report is to provide quick and practical general information on the price history of our products and those of our competitors. In some cases, it may be relevant to have a historical curve to make comparisons in the price behavior of certain SKUs, and that’s why I would like to build this chart.

I thought of a logic to try to filter the number of dates, but I don’t have enough knowledge in Power BI to apply it. Maybe you can help me with its development or tell me if it is possible or not.

1. Create a measure in which it is possible to see the price of one or more selected SKUs on the day before the selected date.
2. Create another measure that compares the price on day D with the price on day D-1.
3. Create a measure with the following conditional:

• If any of the selected SKUs show a variation different from zero (measure 2), the date in question will be kept on the chart. Otherwise, the date will not appear.

[lbendlin]

 it is possible to see the price of one or more selected SKUs on the day before the selected date

This is back to my original question - how many price changes do you expect to happen at most per product per day?  A single change?

In general you're on the right track. Change based reports are much better than snapshot based reports (in my opinion)  but they are harder to implement, so you need to be clear in your sample rate requirements before you tackle this.

[Antonio_Carneir]

That is correct! Each product can have it's price changed only once at a day.
The frequency of that change varies a lot, however. There are some products that are having a steady price for years, while others tend to change more frequently. What i can tell to you is that "Rival" produts (that being SKU's that compete for the same market segment) tend to have the same price behaviour.

[lbendlin]

I would be leaning towards a box and whisker chart type, and would definitely include the number of changes in the chosen interval. If you don't have too many products you could use small multiples.