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

12scml's avatar
12scml
Resolver I
9 years ago

Hi TheoM! I'm still relatively new to this but hopefully this works!

Cost at max date within date range = 
CALCULATE(
         MINX('Fact Table'[Cost]),

         FILTER(

                 ALLSELECTED('Date Table'[Date]),
                 'Date Table'[Date] = MAX('Date Table'[Date]),
         VALUES('Fact Table'[Item]))

 

The first filter should make sure that only the latest date is selected, and the VALUES will make sure that it does this for each item individually! I hope this works! If it doesn't let me know, maybe we can work it out together!