Multiply colums according latest date

Tommyvhod — Wed, 08 Jan 2020 09:42:18 GMT

Hi all

I would like to create a measure but i am stuck. I have a product, for the product different materials with quantities and unit prices.

But in the table I use I have historical data as well. So there are different unit prices with different dates.

What I would like to do is filter out the latest dates for the duplicates and multiply the qty with unit price.

Kind of:

Part 1 mat1 1pcs 5Eur 1.12.2019

Part 1 mat2 0.3kg 8eur 5.12.2019

Part 1 mat3 0.8l 12eur 11.12.2019

Part 1 mat1 1pcs 4.5eur 6.1.2019

Part 1 mat3 0.8l 10eur 5.1.2019

Part 2 mat1 0.4pcs 6 eur 6.9.2019

Part 2 mat2 0.6l 8 eur 12.12.2019

......

There is an update on part1 mat1 and mat3 (material) with the latest date and only that is what i would like to see in the product (part1) costs.

Thank You

Re: Multiply colums according latest date

Parkavi — Wed, 08 Jan 2020 10:56:45 GMT

Please use the below logic

(1) Create a calculated column for finding the latest date for each group(Part, mat). If need, you include the qty in the filter clause

Latestdate =

CALCULATE(MAX('Table'[date]),FILTER('Table','Table'[Part]=EARLIER('Table'[Part])&&'Table'[Material]=EARLIER('Table'[Material])))

(2) Create column Flag for the latest record

Flag = IF('Table'[date]='Table'[latestdate],1,0)

Latest record will be flagged as 1 like below

Now, filter out the old record by filtering the flag <>0. Also fetch the cost of latest date by using filter context in CALCULATE function

Please let me know if this solutions works