Forum Discussion

azaterol's avatar
azaterol
Icon for Helper V rankHelper V
4 years ago
Solved

Special situation with date

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
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

2 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
    Anonymous
    Not applicable
    4 years ago

    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