Forum Discussion
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...
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
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
