Track price changes

[Nickodemus]

I need to track prices of products over time.

The prices of each product can change on any day.

I have a table which records only price changes, eg:

So each time a price changes, a new record will go into the table with corresponding size, and date of the change.

I need to create a report/visual in PBI which shows the price changes over time for each product and size. So in this example, if i had a line chart with dates on x-axis and price on y-axis, it'd show two lines from 01/01/2017-19/02/2017. Where price doesn't change over time, the lines would be horizontal.

I can't seem to figure out how to create this report which would enable me to answer the question - on any given day, what was the price of product A, size Large (or small)?

Any help would be greatly appreciated.

Many thanks.

[ImkeF · Accepted solution]

Thats a pit, I hoped that it would be a bit easier.

You might have to take this approach then:

https://social.technet.microsoft.com/Forums/en-US/3c0ed4e6-bb54-4efb-9fd3-8b950dfccea0/create-a-step...

DimDate needs to be disconnected. So if you have a DimDate already that needs to stay connected for other purposes, you need to create a new one specifically for this measure that will stay disconnected. Make sure to take your Date-field from therer to your report.

[Anonymous · Accepted solution]

I can help write the SQL if you want, but we have enough brain power here... we should be able to pull this off 🙂

I feel like both your table and line chart ... are relying on the Dates table (based on your use of LASTDATE(Dates[Date]) but neither of these are using your Dates table?

If you put Dates "on rows" does that work w/ your measure!?

[v-ljerr-msft]

Hi @Nickodemus,

According to your description above, you may need to use the formula below to add a new calculate column to join Product and Size into a single column first.

Product and Size = Table1[Product] & "-" & Table1[Size]

Then you should be able to add a Line Chart to your report with "Date" as Axis, the created calculate column "Product and Size" as Legend, and "Price" as Values like below.

Regards

[Phil_Seamark]

That looks better.  I misread the original and thought they were after a column showing the delta change from price to price.

Handy enough if you need it but sounds like not what you need.  🙂

[Nickodemus]

Thanks @Phil_Seamark and @v-ljerr-msft, both really useful suggestions.

To be honest, I think it's a combination of the two that i need... Combining the two fields into a single calculated column makes a lot of sense. What's missing there is that you can see in the chart that the 'A-Large' line stops short of the end, because it's where the data stops. What i want to show is effectively a chart of the 'current' price on every day. So if the price doesn't change (i.e. there's no data for the date) the line will be horizontal.

I think this is where the initial suggestion could come in... I guess i'm saying the 'current' price is the 'last price' recorded. It feels like i need a measure(?) which calculates the 'Current' price for any given date, represented by the last price recorded for that product and variant prior to the date being plotted.

E.g. if the price changed on Monday to £10, then on Friday the 'current' price is still £10.

Hope that makes sense... Are you able to suggest the best way to plot this? I.e. what would be the expression for this measure... (sorry, very new to PBI)

[ImkeF]

This sounds as if this might be it: https://social.technet.microsoft.com/Forums/en-US/f208074b-65ba-4a0e-90f4-32b18d4da88f/stock-balance...

[Nickodemus]

Thanks for the link, but I'm not sure that's quite right - it's counting the rows.

I effectively want a table, and for every date (i.e. every day) and every product/size what is the price for that day (which would be the price shown for that product/size combination).

Maybe it's not possible...?

[ImkeF]

It should work if you replace the count-expression by MAX(YourTableName[Price]).

As only one value will be returned, MAX should work fine.

[Nickodemus]

Thanks @ImkeF, but it's not quite right - it's still just providing my a sloped line between two points of the chart... I need this to be horizontal for the dates where the price has not changed, then a vertical where the price changes.

I have amended the expression to this:

Current Rate = 
CALCULATE (
    MAX(Products[Price]),
    FILTER (
        ALL (Dates[Date]),
        Dates[Date]<=Dates[Date]
)
)

This gives me a value for every date, and therefore a horizontal line, however, it's only showing the value as the max price ever, not the max price before a given date. Are you able to decipher what i'm doing wrong?

Also, and crucially, the price won't always go up..., so I don't necessarily want the max price, i need the most current price, even if it's lower...

[ImkeF · Accepted solution]

Thats a pit, I hoped that it would be a bit easier.

You might have to take this approach then:

https://social.technet.microsoft.com/Forums/en-US/3c0ed4e6-bb54-4efb-9fd3-8b950dfccea0/create-a-step...

DimDate needs to be disconnected. So if you have a DimDate already that needs to stay connected for other purposes, you need to create a new one specifically for this measure that will stay disconnected. Make sure to take your Date-field from therer to your report.

[Nickodemus]

Hi @ImkeF,

Apologies for the huuge delay getting back to you. I can't seem to get this to work as you describe in the other post you linked to.

My prices show as horizontal lines on the line chart, for each product only reading as the maximum price.

Please see the below screen shot.

The table in the top left is my data. The line chart and matrix below it are the result of the following measure for "Last Price":

LastPrice = CALCULATE(
     SUM(PriceTracker[Price]),
     FILTER (PriceTracker,
        PriceTracker[Date] 
           = CALCULATE (
             LASTNONBLANK( PriceTracker[Date], 1 ),
             FILTER (
               PriceTracker,
               PriceTracker[Date] <= LASTDATE(Dates[Date])))))

I also have a Date table which is NOT connected to the price table (as instructed in your other post). 

I'm totally at a loss as to how to get this thing to work so that i populates the correct price for each week (effectively filling the gaps), but changes appropriately if there is a price change. This would then result in the correct graph showing horizontal lines for all the weeks where the price is unchanged, and then the rise/fall when the price changes.

I'd be extremely grateful if someone is able to guide me through a solution to this... I can't be the only one who's needed to do this....?

[ImkeF]

Have you checked that you use the date fields from your disconnected date-table in your reports?

It's best to hide the date-field from the PriceTracker-Table from the client-tools.

[Nickodemus]

Yea - I double checked that before I sent the post... It's very odd.

[ImkeF]

Then we need to call in the experts: @Phil_Seamark @OwenAuger @Greg_Deckler @Anonymous @Sean anyone an idea here?

[Nickodemus]

Thanks @ImkeF. 

Perhaps another option would be to create the 'full' table in SQL and import the full table to PBI? 

So i would take my price tracking table, and fill in the gaps with a price for every week up to the present.... Do you know if this is possible, and if so.... any idea how? My SQL is worse than my PBI knowledge...

[Vvelarde · Accepted solution]

@Nickodemus

hi, the graphic wanted should be look like this?.

If the answer is yes, please follow this few steps

1. Create a calendar table with the dates (Disconnected)

2. Create a measure

LastPrice =
IF (
    HASONEVALUE ( MyCalendar[Date] ),
    CALCULATE (
        LASTNONBLANK ( Table1[Price], Table1[Price] ),
        FILTER ( Table1, Table1[Date] <= VALUES ( MyCalendar[Date] ) )
    )
)

3. Insert a Visual with

Date from Calendar Table

Size in Legend

LastPrice in Values

Also a Slicer to select the product.

[ImkeF]

Thx @Vvelarde,

but your measure (red) doesn't look so good with my new data:

I've updated the file - maybe you could have a look please? It seems to keep the maximum instead of the latest value.

Thanks a lot,

[Vvelarde]

@ImkeF

Yes i have a problem with the measure, only works when the value is increasing no decreasing.

Your formula looks great.

Another way to solve but i think with less performance will be:

LastPrice2 =
IF (
    HASONEVALUE ( DimDate[Date] ),
    CALCULATE (
        MIN ( Table1[Value] ),
        TOPN (
            1,
            FILTER ( Table1, Table1[Date] <= VALUES ( DimDate[Date] ) ),
            Table1[Date], DESC
        )
    )
)

[OwenAuger]

@ImkeF, @Nickodemus,@Vvelarde

Just another suggestion to add to those given already 🙂

Assuming you have Prices & Date table, here is a measure to return the most recent price.

The important part (in red) returns, from the Date table, the last date that appeared in the Prices table up to the max selected date.

Last Price = 
CALCULATE (
    AVERAGE ( Prices[Price] ), // Arbitrary aggregation, assuming there is only one price per date
    CALCULATETABLE (
        CALCULATETABLE ( LASTDATE ('Date'[Date] ), Prices ),
        DATESBETWEEN ( 'Date'[Date], BLANK (), MAX ( 'Date'[Date] ) )
    )
)

This measure is not set up to aggregate across products but could be extended to do that.

[ImkeF]

I've uploaded the original xlsx with my working solution here: https://onedrive.live.com/edit.aspx?cid=de165ddf5d02daff&page=view&resid=DE165DDF5D02DAFF!87383&parI...

maybe you can figure out whats wrong/different with your solution.

[ImkeF]

Do you have bidirectional filtering from your PriceTracker-table to others?

[Anonymous · Accepted solution]

I can help write the SQL if you want, but we have enough brain power here... we should be able to pull this off 🙂

I feel like both your table and line chart ... are relying on the Dates table (based on your use of LASTDATE(Dates[Date]) but neither of these are using your Dates table?

If you put Dates "on rows" does that work w/ your measure!?