Most recent values from another table

[François]

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 🙂 

[Mariusz · Accepted solution]

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 · Accepted solution]

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]

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 · Accepted solution]

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

[François]

Ok, I understand it better and it's great to have the code, I can use your example and adapt it.

A last question, in my price list, I have the currency also. All prices for an item will have the same currency, always. But different items may have different currency, as we have different suppliers. So I need to also have a column "purchase currency". 

I don't know how to get that, I'm trying to create a measure, but I don't know how to say "unique value" or "first value" or "last value" (since it's always the same for each item). How can I extract it ? It seems like a simple pb, but I can't figure out a simple solution 😞

[Mariusz]

Hi @François 

You can use MIN(), MAX(), SELECTEDVALUE() or use X Functions like SUMX(<table>, <expression>)

Best Regards,
Mariusz

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

Please feel free to connect with me.
LinkedIn

[amitchandak]

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]

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 ? 

[François]

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