Forum Discussion
Helper IMost recent values from another table
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.
LinkedInHi 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.
LinkedInBest Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
Community ChampionHi 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] )
)
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
Helper IThanks 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
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.
LinkedInBest 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 😞