Forum Discussion

mmunoa's avatar
mmunoa
Regular Visitor
5 years ago
Solved

Tracking cost increase or decrease

Hi!   First of all, thanks in advance for the help!   I have to calculate the increase of costs for different product from different suppliers between dates. So for that I have a table called "p...
  • amitchandak's avatar
    amitchandak
    5 years ago

    mmunoa , Can you try the Min Cost Like

     

    Cost_MinDate =
    var firstdate = maxx('price_history','price_history'[UPDATE DATE])
    return
    calculate (lastnonblankvalue('price_history'[UPDATE DATE],[COST]),
    filter (all('price_history'),'price_history'[UPDATE DATE]<firstdate))

     

    or

     

    Cost_MinDate =
    var firstdate = maxx('price_history','price_history'[UPDATE DATE])
    return
    calculate (lastnonblankvalue('price_history'[UPDATE DATE],[COST]),
    filter (all('price_history'),'price_history'[UPDATE DATE]<firstdate && 'price_history'[product] =max('price_history'[product])))

     

    or

     

    Cost_MinDate =
    var firstdate = maxx('price_history','price_history'[UPDATE DATE])
    return
    calculate (lastnonblankvalue('price_history'[UPDATE DATE],[COST]),
    filter (all('price_history'),'price_history'[UPDATE DATE]<firstdate && 'price_history'[product] =max('price_history'[product] && 'price_history'[SUPPLIER] =max('price_history'[SUPPLIER])))

     

     

     

     

     

amitchandak's avatar
amitchandak
Super User
5 years ago

mmunoa , Can you try the Min Cost Like

 

Cost_MinDate =
var firstdate = maxx('price_history','price_history'[UPDATE DATE])
return
calculate (lastnonblankvalue('price_history'[UPDATE DATE],[COST]),
filter (all('price_history'),'price_history'[UPDATE DATE]<firstdate))

 

or

 

Cost_MinDate =
var firstdate = maxx('price_history','price_history'[UPDATE DATE])
return
calculate (lastnonblankvalue('price_history'[UPDATE DATE],[COST]),
filter (all('price_history'),'price_history'[UPDATE DATE]<firstdate && 'price_history'[product] =max('price_history'[product])))

 

or

 

Cost_MinDate =
var firstdate = maxx('price_history','price_history'[UPDATE DATE])
return
calculate (lastnonblankvalue('price_history'[UPDATE DATE],[COST]),
filter (all('price_history'),'price_history'[UPDATE DATE]<firstdate && 'price_history'[product] =max('price_history'[product] && 'price_history'[SUPPLIER] =max('price_history'[SUPPLIER])))

 

 

 

 

 

mmunoa's avatar
mmunoa
Regular Visitor
5 years ago

Thanks amitchandak for helping!

 

I tested it with the three options but it doesn't work, but i forgot to say something:

I also have a calendar table related to the 'price_history'[UPDATE DATE] column.

 

Maybe thats important for understanding better the problem, because when if filter between two dates, I do it on the date table (Which is supposed to filter the price_history table)

 

 

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    mmunoa , Try like

    Cost_MinDate =
var firstdate = maxx('Date','Date'[DATE])
return
calculate (lastnonblankvalue('Date'[DATE],[COST]),
filter (all('Date'),'Date'[ DATE]<firstdate))