topic Re: Tracking cost increase or decrease in DAX Commands and Tips

Tracking cost increase or decrease

mmunoa — Tue, 29 Sep 2020 13:35:32 GMT

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	SUPPLIER	UPDATE DATE
PRODUCT_1	17,79	SUPPLIER_A	February-19
PRODUCT_1	19,21	SUPPLIER_B	April-19
PRODUCT_2	39,98	SUPPLIER_A	February-19
PRODUCT_3	73,43	SUPPLIER_B	April-19
PRODUCT_1	21,05	SUPPLIER_A	June-20
PRODUCT_2	53,85	SUPPLIER_A	June-20
PRODUCT_3	101,25	SUPPLIER_B	July-20
PRODUCT_1	23,01	SUPPLIER_B	July-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!

Re: Tracking cost increase or decrease

amitchandak — Tue, 29 Sep 2020 13:51:40 GMT

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 && 'price_history'[product] =max('price_history'[product])))

Re: Tracking cost increase or decrease

mmunoa — Tue, 29 Sep 2020 19:50:49 GMT

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)

Re: Tracking cost increase or decrease

amitchandak — Wed, 30 Sep 2020 00:34:46 GMT

mmunoa , Try like

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

Re: Tracking cost increase or decrease

mmunoa — Fri, 02 Oct 2020 12:39:17 GMT

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!!!