Forum Discussion
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 updated, this results in one or more new entries in the fact table (depending on the number of cost components for the specific item).
I need a measure that returns the cost (per component) for an item at a certain date. The measure needs to check the date in the fact table and has to return the cost for the latest date for that item in the fact table which is not later than this certain date.
For example: I need cost prices as per 31 December 2016
Item 1 has entries for March 31 2016, June 15 2016, November 11 2016, February 15 2017
Item 2 has entry for December 31 2016
Item 3 has entries for June 15 2016 and February 15 2017
The measure has to select the underlined dates (in the end it has to calculate the cost price for that date)
I have been puzzling a lot but havent been able to find the solution yet. Can anyone help me out?
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
8 Replies
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
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
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!
