Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Show changes in price

Hi everyone!

I have been struggling with this problem for a couple of days, but I hope some of you can assist me in solving it.

First, let me explain what I am dealing with and what I am trying to achieve:

All of my efforts are based on a simple table, which gets its data from multiple different excel sheets. That part works as I intended it to. Here you can see a modified version of the table (I changed part no and prices, but the structure is the same)
Project Number: A project contains multiple parts
Date: Date on which the report was downloaded. Prices change over time

Project NumberDatePart noProject no_Part No Part DescriptionPriceUsed in assemblyQuantity in this assemblyPart Group
12321.01.2020123456123_123456text101a2Basic Parts
12328.01.2020123456123_123456text121a2Basic Parts
23421.01.20204567A234_4567Atext52a2Additional Parts
23428.01.20204567A234_4567Atext32a2Additional Parts

Part Number: Specifies a part; can be used in multiple Assemblies and Project. BUT in different projects the same Part No may have DIFFERENT prices.
Project no_Part No: I created this custom column to be able to distinguish the same part no from different projects --> so I know which price is correct
Part Description: Always the same for the same part no
Price: Depends on the date, project, and PartNo
Used in Assembly: A part can be used in multiple assemblies, but one assembly always belongs to the same project no
Quantity in Assembly: It is needed to calculate the price of an assembly.
Part Group: Can be used to show how the prices of all "Basic Parts, "Additional Parts"... changes over time

What am I trying to achieve?
I want to show how the price of one part changes over time. This needs to be based on the "Project no_Part No", because there may be different prices from the same date for the same part no.
The reports are pulled every week, so dates should be converted to "YYYY"&"Weeknum".
Furthermore, I want to show how the prices of one assembly changes over time.
In my first trials, I created measures to output the price of 1 week ago, 1 Month ago, 1 Q,...

Price -1W =
Var oneweekago = CALCULATE(YEAR (DATEADD ( 'Date'[Date]; -7; DAY )) & FORMAT (WEEKNUM (DATEADD ('Date'[Date]; -7; DAY );2); "00" ); 'Date'[Date]=TODAY())
return

CALCULATE(SUM('All Projects'[Price]); 'All Projects'[Weeknum]= oneweekago)

It did not work. When placed in a Table visual on its own, it showed me the sum of all prices of all parts one week ago, but as soon as I wanted to add "Project no_Part No" as another column to the table (to see the old price of each part on its own), it broke.

I feel I "just" need to get the Measures to get the old prices to work. I think I know how to go from there --> G´Create a measure for the delta in price and so on. But I am completely stuck on the measures outputting the old prices.

Any help is greatly appreciated! If anything is unclear, please ask!

 

Best regards and thanks in advance

Joost 🙂 

Need Help
Tutorial Requests
  • JarroVGIT's avatar
    JarroVGIT
    6 years ago

    Hi Anonymous ,

    I have updated the file here.

    The measure for 'delta compared to last week' is this:

    LastWeek = 
VAR _curYearWeek = SELECTEDVALUE(DateTable[yearweek])
RETURN
SWITCH(TRUE(),
    CALCULATE(MAX(Table3[Price]), DateTable[yearweek] = (_curYearWeek-1)) = BLANK(), BLANK(),
    MAX(Table3[Price]) = BLANK(), BLANK(),
    MAX(Table3[Price])-CALCULATE(MAX(Table3[Price]), DateTable[yearweek] = (_curYearWeek-1)))

    There are a couple of things I had to do before I got it to work. First one is to make sure the DateTable is a Whole Number type:

    Second, you need to set the Y-axis of the horizontal bar chart to categorical (otherwise yearweek like 202004 will be displayed as 0.2m:

    Have a look at the pbix, again it is Table3, DataTable and page JoostM 🙂 Ignore the rest, those are for other questions 🙂

     

    Kind regards

    Djerro123

    -------------------------------

    If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

    Keep those thumbs up coming! 🙂

     

3 Replies

  • JarroVGIT's avatar
    JarroVGIT
    Resident Rockstar
    6 years ago

    Hi Anonymous ,

    I would advise for you to use a date table for this. The date table can be a calculated table like this:

    DateTable = ADDCOLUMNS(CALENDAR("01/01/2017", "31/12/2020"), 
     "yearweek", 
     CONCATENATE(FORMAT(YEAR([Date]), "####"), FORMAT(WEEKNUM([Date]), "0#")))

    Next you need to create a relationship between DateTable[Date] and Table3[Date]. Then you create a slicer based on Project number and a Line graph with DateTable[yearweek] as axis, legend = Part no and Values is Price. Result is this:

     

    This shows you price of items over time within projects. I don't understand why you are looking at measures to be honest? My PBIX is here for reference (Page "JoostM", Table3 and DataTable, please ignore all other pages and tables as they are for other questions. Let me know if this helps you! If you want another output, please describe what visual you are trying to create 🙂

     

    Kind regards

    Djerro123

    -------------------------------

    If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

    Keep those thumbs up coming! 🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hey JarroVGIT,

      thank you so much for your quick reply! 

      The line graph is working for me, but it's not really what I am trying to achieve. I forgot to tell, how I wanted to vizualize the data.

       

      I am trying to get something like this:

      So basically a horizontal bar chart. Each bar is for one Project-PartNo, while the value displayed should show the difference between last weeks/months/quarters/... price. That's where I thought a measure is needed. 

       

      I created the date table. 

       

      Best regards

      Joost 🙂 

      • JarroVGIT's avatar
        JarroVGIT
        Resident Rockstar
        6 years ago

        Hi Anonymous ,

        I have updated the file here.

        The measure for 'delta compared to last week' is this:

        LastWeek = 
VAR _curYearWeek = SELECTEDVALUE(DateTable[yearweek])
RETURN
SWITCH(TRUE(),
    CALCULATE(MAX(Table3[Price]), DateTable[yearweek] = (_curYearWeek-1)) = BLANK(), BLANK(),
    MAX(Table3[Price]) = BLANK(), BLANK(),
    MAX(Table3[Price])-CALCULATE(MAX(Table3[Price]), DateTable[yearweek] = (_curYearWeek-1)))

        There are a couple of things I had to do before I got it to work. First one is to make sure the DateTable is a Whole Number type:

        Second, you need to set the Y-axis of the horizontal bar chart to categorical (otherwise yearweek like 202004 will be displayed as 0.2m:

        Have a look at the pbix, again it is Table3, DataTable and page JoostM 🙂 Ignore the rest, those are for other questions 🙂

         

        Kind regards

        Djerro123

        -------------------------------

        If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

        Keep those thumbs up coming! 🙂