Forum Discussion

Nickodemus's avatar
Nickodemus
Helper III
9 years ago
Solved

Track price changes

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.

Need Help
  • ImkeF's avatar
    ImkeF
    9 years ago

    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-chart-using-powerpivot?forum=sqlkjpowerpivotforexcel

     

    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's avatar
    Anonymous
    9 years ago

    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!?

     

  • Vvelarde's avatar
    Vvelarde
    9 years ago

    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.

     

     

24 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi Nickodemus

     

    You could try and add this calculated column

     

    Diff = 
var OuterSize = 'Prices'[Size]
var OuterProduct = 'Prices'[Product]
var OuterDate = 'Prices'[Date]
Var MyLastDate =  CALCULATE(
                LASTDATE(
                    'Prices'[Date]),
                    
                    FILTER(
                        ALL('Prices'),
                        'Prices'[Size] = OuterSize
                        && 'Prices'[Date] < OuterDate
                        && 'Prices'[Product] = OuterProduct
                        )
                  )

VAR MyLastPrice = CALCULATE(MAX('Prices'[Price]),FILTER(ALL('Prices') , 
            'Prices'[Date] = MyLastDate && 
            'Prices'[Size] = OuterSize &&        
            'Prices'[Product] = OuterProduct
            )
            )
            
Var Result =   'Prices'[Price] - MyLastPrice           
return IF(MyLastPrice <> BLANK() , Result , blank())

    Which for me returns this which I can use to build a visual

     

    • CB's avatar
      CB
      Frequent Visitor
      8 years ago

      Thank you for this code to calculate the delta between the current value and previous of the same object. In my case, I'm trying to show the difference of Blood Pressure readings (one for the upper number (systolic) and one for the lower (diastolic) ). I have a  line chart showing one line for systolic and one for diastolic. I would like to show the delta between each reading of each line. ideally, I would like to be able to see the delta by hovering over each interval to see the delta from the prior interval. Is that possible? What kind of property/visual would do that? 

       

      Another idea is to use more lines and/or columns to present the delta but that is not so preferred. 

       

      Do you have any thoughts on how to do the first option- hovering over to see the delta? 

       

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    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.:smileyhappy:

     

    Regards

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      9 years ago

      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's avatar
        Nickodemus
        Helper III
        9 years ago

        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)