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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi TheoM,

 

According to your description, you want to get the nearest date of the selected calendar date, right?

 

If this is a case, you can refer to below sample.

 

Fact table.

 

Measures:

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

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

 

 

 

If above not help, can you provide some sample data to analysis?

 

Regards,

Xiaoxin Sheng

TheoM's avatar
TheoM
Helper 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
      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