Forum Discussion

François's avatar
François
Helper I
6 years ago
Solved

Most recent values from another table

I have a table of products, and a table of prices.

Prices table contains the product ID, as well as the date, and the type of product (purchase or sales)

 

I want to display a list of 

Product ID
Latest purchase price of last year
Latest purchase price of current year

Latest sales price of last year
Latestsales price of current year

 

And I'm really not sure how to do that easily. Would some have suggestions ? Thanks in advance 🙂 

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi François 

     

    Try this.

    Latest purchase price of current year = 
VAR __lastDate =   
CALCULATE(
    MAX( 'Table'[Transaction Date] ),
    ALL( 'Table' ),
    VALUES( 'Table'[Product ID] )
)
RETURN 
CALCULATE(
    SUM( 'Table'[Purchase Price] ),
    TREATAS( { __lastDate }, 'Calendar'[Date] )
)

     

    Latest purchase price of last year = 
VAR __year = YEAR( MAX( 'Table'[Transaction Date] ) )
VAR __lastDate =   
CALCULATE(
    MAX( 'Table'[Transaction Date] ),
    ALL( 'Table' ),
    VALUES( 'Table'[Product ID] ),
    'Calendar'[Year] < __year
)
RETURN 
CALCULATE(
    SUM( 'Table'[Purchase Price] ),
    TREATAS( { __lastDate }, 'Calendar'[Date] )
)

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi François 

     

    First I'm calculating the last date the product was purchased, and later using this date to get a price, I used SUM ( as you need to use an aggregation ) but you can use MAX or SELECTEDVALUE.

     

    With last year the same just excluding the current year.

     

    And yes you can replace the 'Calendar'[Date] with date in your table.

     

    To create the sales price you can just copy the code and create another Measure replacing Purchase column with Sales. 

     

    Please see the attached file with a solution.

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Few things can be achieved using the measure. If you can share sample data and sample output, that will help.

    The way to get values one table to another. Conditions can change

     

    New column in Table 1 = maxx(filter(table2,table1[customer] = table2[customer] && table2[option]="construction",table2[value])

New column in Table 1 = maxx(filter(table2,table1[Attribute] = table2[name] && table1[project] = table2[project]),table2[name])
    • François's avatar
      François
      Helper I
      6 years ago

      Here is an example of data : 

       

      Products

      AAA
      BBB

       

      Prices

      AAA   2019-01-01   Purchase 100

      AAA   2019-01-01   Sales 130
      AAA   2019-06-01   Purchase 110

      AAA   2020-01-01  Purchase 120

      AAA   2020-02-01  Sales 140

      BBB   2019-01-01   Purchase 150

      BBB   2019-06-01   Sales 180

       

      Expected result :
      Product ID  - latest purchase price (Y-1) - latest sales price (Y-1) - latest purchase price - latest sales price
      AAA   110 130  120  140 
      BBB    150 180  150  180

    • François's avatar
      François
      Helper I
      6 years ago

      Thanks for your fast answer.
      I have tried to do this : 

      create a measure calculating the latest purchase price, sales price, and same for previous year. This is working. I have 4 dates in those measures.

      Then I tried to create another measure, giving me the average purchase price with a filter on the date, using the measures I have calculated. But this one is not working, it gives me wrong values (apparently, it's not filtering right and calculate an average on multiple values). What am I doing wrong ? 

       

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi François 

     

    Try this.

    Latest purchase price of current year = 
VAR __lastDate =   
CALCULATE(
    MAX( 'Table'[Transaction Date] ),
    ALL( 'Table' ),
    VALUES( 'Table'[Product ID] )
)
RETURN 
CALCULATE(
    SUM( 'Table'[Purchase Price] ),
    TREATAS( { __lastDate }, 'Calendar'[Date] )
)

     

    Latest purchase price of last year = 
VAR __year = YEAR( MAX( 'Table'[Transaction Date] ) )
VAR __lastDate =   
CALCULATE(
    MAX( 'Table'[Transaction Date] ),
    ALL( 'Table' ),
    VALUES( 'Table'[Product ID] ),
    'Calendar'[Year] < __year
)
RETURN 
CALCULATE(
    SUM( 'Table'[Purchase Price] ),
    TREATAS( { __lastDate }, 'Calendar'[Date] )
)

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

    • François's avatar
      François
      Helper I
      6 years ago

      Thanks for the reply Mariusz


      I'm trying to user your solution. From what I understand, you calculate first the latest price for the item and put it in the __lastDate Var.

      Then you calculate a sum of prices, but you filter on the right date to do a sum of only one value. Am I right ? 

      My problem to apply this is :

      -you are using a Calendar table apparently when filtering on the sum. Do I need to create a date table ? Or can't I filter the same way on the Date column directly ? 

       

      Also, in your solution, you are not filtering on the type of price. My price table is mixing both purchase & sales prices. So I guess I have to filter in your fisrt CALCULATE (to find the latest of the purchase prices), then also in the second one (to avoid summing on a sales price that could have the same date as the purchase one) ?

      • Mariusz's avatar
        Mariusz
        Community Champion
        6 years ago

        Hi François 

         

        First I'm calculating the last date the product was purchased, and later using this date to get a price, I used SUM ( as you need to use an aggregation ) but you can use MAX or SELECTEDVALUE.

         

        With last year the same just excluding the current year.

         

        And yes you can replace the 'Calendar'[Date] with date in your table.

         

        To create the sales price you can just copy the code and create another Measure replacing Purchase column with Sales. 

         

        Please see the attached file with a solution.

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        LinkedIn

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        LinkedIn