Forum Discussion

Nickodemus's avatar
Nickodemus
Icon for Helper III rankHelper 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 recor...
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.

     

     

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft 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?