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 "price_history" where every supplier's price list is registered the day it is sended to me, like the following:

 

* The "UPDATE DATE" is a date type colum, not a string like here in the example.

 

PRODUCT  PRICE  SUPPLIERUPDATE DATE
PRODUCT_1  17,79  SUPPLIER_A    February-19
PRODUCT_119,21SUPPLIER_BApril-19
PRODUCT_239,98SUPPLIER_AFebruary-19
PRODUCT_373,43SUPPLIER_BApril-19
PRODUCT_121,05SUPPLIER_AJune-20
PRODUCT_253,85SUPPLIER_AJune-20
PRODUCT_3101,25SUPPLIER_BJuly-20
PRODUCT_123,01SUPPLIER_BJuly-20

 

And I have the following measures:

 

COST = SUMX('price_history','price_history'[COST])

 

Cost_MaxDate =
var lastdate = maxx('price_history','price_history'[UPDATE DATE])
return
calculate ([COST],
filter ('price_history','price_history'[UPDATE DATE]=lastdate))

 

 

Cost_MinDate = 

var firstdate = minx('price_history','price_history'[UPDATE DATE])
return
calculate ([COST],
filter ('price_history','price_history'[UPDATE DATE]=firstdate))
 
and then with those two measures (Cost_MinDate and Cost_MaxDate) I finally calculate the % of increase or decrease between values.
But the problem that I have is that if I filter the date for example between June-19 and July-20 I will have only one value for each product (So % variation is 0), and in that case I don`t want that. In fact, I would like to consider the last previous cost for each item.
 
Do you know what could I do?
 
Thanks!
  • 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])))

     

     

     

     

     

4 Replies

Replies have been turned off for this discussion
  • 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))
    • mmunoa's avatar
      mmunoa
      Regular Visitor
      5 years ago

      Thank you very much! It didnt worked just like its expressed here but it gave me the tools and "way of thinking" to solve it.

       

      Thank you!!!