Return value based on max date within date range

[TheoM]

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?

[v-shex-msft]

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]

Hi @v-shex-msft

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

[v-shex-msft]

Hi @TheoM,

Can you please share some sample data to test?

Regards,

Xiaoxin Sheng

[TheoM]

Hi @v-shex-msft

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

[v-shex-msft · Accepted solution]

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]

Hi @v-shex-msft

That does the job! The formula works perfectly. Thanks

Theo

[v-shex-msft]

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.

Regards,

Xiaoxin Sheng

[12scml]

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!