Special situation with date

[azaterol]

Hello everyone,

I'm really frustrated and I hope you can help me.
I have two tables that only match the article number. Both tables have a date. If the date from the log table is greater than or equal to the date from the item table, the price should appear in the column and apply from then until a new price is set.

You have an idea for a measure ?

Example:

Initial situation

Item table:

Item		Date
A		01/30/2020
A		01/31/2020
A		02/02/2020
A		02/23/2020
A		02/25/2020

Log table:

Item		Log date		Sell price		Buy price
A		01/30/2020		$5,00		$2,00
A		02/03/2020		$4,00		$3,00

Expected sitation:

Item table:

Item		Date		Sell price		Buy price
A		01/30/2020		$5,00		$2,00
A		01/31/2020		$5,00		$2,00
A		02/02/2020		$5,00		$2,00
A		02/23/2020		$4,00		$3,00
A		02/25/2020		$4,00		$3,00

[v-yalanwu-msft · Accepted solution]

Hi, @azaterol ;

Try this measure:

Sell price = 
var _maxdate=CALCULATE(MAX('Log'[Log date]),FILTER('Log',[Log date]<=MAX('Item'[Date])))
return CALCULATE(MAX('Log'[Sell price]),FILTER('Log',[Log date]=_maxdate))

Buy price = 
var _maxdate=CALCULATE(MAX('Log'[Log date]),FILTER('Log',[Log date]<=MAX('Item'[Date])))
return CALCULATE(MAX('Log'[Buy price]),FILTER('Log',[Log date]=_maxdate))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi  @ Azaterol,

Please find the code for your 2 requested columns

Sell_Price =

var selitem='Item'[Item]

var datemax='Item'[Date]

var calcdate=calculate(max('Log'[Log date]),'Log'[Item]=selitem&&'Log'[Log date]<=datemax)

var result=calculate(max('Log'[Sell price]),'Log'[Item]=selitem&&'Log'[Log date]=calcdate)

return

result

Sell_Price =

var selitem='Item'[Item]

var datemax='Item'[Date]

var calcdate=calculate(max('Log'[Log date]),'Log'[Item]=selitem&&'Log'[Log date]<=datemax)

var result=calculate(max('Log'[Sell price]),'Log'[Item]=selitem&&'Log'[Log date]=calcdate)

return

result