Forum Discussion

TheoM's avatar
TheoM
Icon for Helper I rankHelper 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
Icon for Helper I rankHelper I
9 years ago

Hi Anonymous

The date i want to select is the date equal to or before the selected calendar date, so I could skip half of your code, but it was very helpful! I succeeded in returning the right date from the fact table. I dit not yet succeed to return the correct prices, because the fact table contains several rows that need to be returned (per item there can be 1 or more amounts, appending on the number of cost components for an item (material, wages, machine cost etc). I think an extra argument in de lookupvalues formula will do the job but i havent had the time to fix that. I will keep you informed, Thanks so far

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi TheoM,


Can you please share some sample data to test?

 

Regards,

Xiaoxin Sheng

  • TheoM's avatar
    TheoM
    Icon for Helper I rankHelper 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
        Icon for Helper I rankHelper I
        9 years ago

        Hi Anonymous

         

        That does the job! The formula works perfectly. Thanks

         

        Theo