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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nickodemus
Helper III
Helper III

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:

Screen Shot 2017-03-27 at 16.40.59.png

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.

3 ACCEPTED SOLUTIONS

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Anonymous
Not applicable

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

 

View solution in original post

@Nickodemus

 

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

 

 

Image.png

 

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.

 

 




Lima - Peru

View solution in original post

24 REPLIES 24

@Anonymous - Thanks for that suggestion! Replacing the week numbers with the dates worked a treat, and gave me the chart i wanted. @ImkeF - it seemed that this was the major difference in our solutions, so it's now kinda working! 🙂

 

I've tried many further combinations to try to get the visual to work for week numbers from my date table, but whatever i do, it always just gives me the flat line at the current max. Do you know if there is a way to elaborate on the solution so that I'm able to set the week numbers as the x-axis of my line chart?

 

@Vvelarde & @OwenAuger, thanks also to you both. I tried both your solutions too, and got them to work.

 

 

I guess a waterfall chart is a good way of looking at it... but i can get this to work either...

Phil_Seamark
Employee
Employee

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

 

Price Diff.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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? 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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