Return a corresponding value from another table based on calculated measure

[Anonymous]

Hi!

I have been struggling with this kind of task for days now. I made a calculated measure that will return an age of a machine in the given time (see a picture below). 

The age of the machine defines a buyback rate that I would like to find from another table to calculate the buyback price for the machine. I have a table that has two columns Age (months) and Buyback rate %, creating a calculated column is not an option since I want this to work dynamically. All the solutions that I have tried so far have failed and I only get blank values as a result. Any help on that one? 

[Fowmy · Accepted solution]

@Anonymous 
Not enough information but can you try the following meaure:

RateMeasure =
VAR __AgeSelected =
    MAX ( ThisTable[MachineAge] )
RETURN
    CALCULATE (
        MAX ( AgeTablename[Buyback rate %] ),
        AgeTablename[Age] = __AgeSelected
    )

 Replace the table and columns names as per your model
 

[Fowmy · Accepted solution]

@Anonymous 
Not enough information but can you try the following meaure:

RateMeasure =
VAR __AgeSelected =
    MAX ( ThisTable[MachineAge] )
RETURN
    CALCULATE (
        MAX ( AgeTablename[Buyback rate %] ),
        AgeTablename[Age] = __AgeSelected
    )

 Replace the table and columns names as per your model
 

[Anonymous · Accepted solution]

Hey! The measure that you posted was unfortunately not working directly (blame is on me, I didn't provide enough information). Luckily I got the measure working, adding the rounddown function on the age and deleting the Max statement from "var age" function was the key as shown below.  

Machine age := 
var age = (MAX(vDimCalendar[Date]) - MAX(Buybacks[Delivered (month/year)])) / 365  * 12
return
ROUNDDOWN(age, 0)
---------------------------------------------------------------------------------------------
Buy-back policy rate := 
VAR __AgeSelected =
     'Measures table'[Machine age] 
RETURN
    CALCULATE (
        MAX ( 'Buyback policy rates'[Trade-in] ),
        'Buyback policy rates'[Month] = __AgeSelected
    )