Forum Discussion

Tommyvhod's avatar
Tommyvhod
Icon for Helper II rankHelper II
6 years ago
Solved

Multiply colums according latest date

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's avatar
    Parkavi
    6 years ago

     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
     
     
     
     

1 Reply

  • Parkavi's avatar
    Parkavi
    Frequent Visitor
    6 years ago

     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