Multiply colums according latest date

[Tommyvhod]

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

[Parkavi · Accepted solution]

 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