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 we...
  • 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
     
     
     
     
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