Forum Discussion

TheoM's avatar
TheoM
Helper I
9 years ago
Solved

Return value based on max date within date range

I have a fact table, consisting of: Item Cost component (an item can have more than one cost component) Date Amount   And i have a date table too.   Each time when the price for an item is up...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    HI TheoM,

     

    According to your description, you want get the amount of nearest date, right?
    If this is a case, you can modify the formula and use the amount instead the component:

     

    nearstDate = 
var current_Item=LASTNONBLANK('fact'[Item No],[Item No])
var Nearest_Date_before = MAXX(FILTER(ALLSELECTED('fact'),[Item No]=current_Item&&[Date]<=LASTDATE(ALLSELECTED('CALENDAR'[Date]))),[Date])
return
Nearest_Date_before

LastCost = 
var current_Item=LASTNONBLANK('fact'[Item No],[Item No])
return
LOOKUPVALUE('fact'[Amount],[Item No],current_Item,[Date],[nearstDate])

     

    Then add a slicer to filter on date to let the formula works.

     

    Regards,

    Xiaoxin Sheng

TheoM's avatar
TheoM
Helper I
9 years ago

Hi Anonymous

 

That does the job! The formula works perfectly. Thanks

 

Theo

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi TheoM,

 

Actually, I think you can add variable to store current components and add it to 'lookupvalue' formula to filter more detailed.

I'm glad to know that the formula helps for you.:smileyhappy:

 

Regards,

Xiaoxin Sheng