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

I don't know how to upload data, so I attached a few screenshots.

 

This is what my fact table looks like. Note that a change of the amount of any of the components leads to a new entry for all components.

Fact table

The cost price consists of one or more components and I need a measure to sum the amounts of those records in which the date is the latest date on or before the selected date from the calendar. I have aleady a measure to determine this date:

 

Relevant date =
var CurrentItem = LASTNONBLANK(FactTable[Item];FactTable[Item])
return
MAXX(FILTER(ALLSELECTED(FactTable);FactTable[Item]=CurrentItem&&FactTable[Date]<=LASTDATE(ALLSELECTED(Calendar[Date])));FactTable[Date])

 

I didn't succeed to create a measure which selects the amout where the date matches the relevant date. 

 

With this measure the output should look like this:

Result of measure

I hope you can help me out.

 

Best regard,

Theo

Anonymous's avatar
Anonymous
Not applicable
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