Percentage change for recent two rows shown in the table

[borism84]

Dear all, 

I hope very much you can help  me to calculate the percentage change row by row. 

I have a table with product SKU and some attributes like size, color and others which are not shown here but can be added as for example article group, season etc. 

The columns show the stock level per SKU per day. I would like to have the percentage change per article SKU for the last two values shown in table, for example for the first product the change would be (279-281)/281= -0.7%. 

 Could you advise how i need to do it? 

Many thanks for your help

[Anonymous · Accepted solution]

You're right. I was just testing/creating an example file and found out. Solution: use DATEADD (see formula below), this function works the about same, but does support DAY as interval. You'll could use Parallelperiod if you want to calculate the difference of say a average per month or year. 

Here the formula for date add and printscreen of the result, i'll also try to upload my test .pbix file.

Diff Last Day = (
calculate(sumx(Stock_Table;Stock_Table[Stock])) -
calculate(sumx(Stock_Table;Stock_Table[Stock]),DATEADD(Stock_Table[Date],-1,DAY)))/
calculate(sumx(Stock_Table;Stock_Table[Stock]))

Stock Yesterday = calculate(sumx(Stock_Table;Stock_Table[Stock]),DATEADD(Stock_Table[Date],-1,DAY))

[Anonymous]

You can find the test file here.

[borism84]

Re: Percentage change for recent two rows shown in the table

 [ New ] 

44m ago - last edited 42m ago

Dear @The_Coon

Its absolutely great. Thank you sooo much for the example and the detailed formula. 

Just one more detail. 

When I have selected a date range of two days 16.01.2019 - 17.01.2019, the table is workling perfectly. 

But if I increase the date selection even by one day more, I get an error messsage that the dateadd function expects a contigious selection. 

Does it mean, that same SKUs (Key= article SKU) should be in the table for all days. So for example if article A has stock values for 16.01.2019 and 17.01.2019 but not for 18.01.2019, I would get this error message? 

If so, any possibility to overcome it in the sence that if article SKU (in the table "KEY" is not found for the date, then the stock value for this SKU should be zero). 

Thank you sooo much @The_Coon for your time and support. I would have still be looking for the solution for my problem.  VERY APPRICIATED

[borism84]

Dear @The_Coon

Its absolutely great. Thank you sooo much for the example and the detailed formula. 

Just one more detail. 

When I have selected a date range of two days 16.01.2019 - 17.01.2019, the table is workling perfectly. 

But if I increase the date selection even by one day more, I get an error messsage that the dateadd function expects a contigious selection. 

Does it mean, that same SKUs (Key= article SKU) should be in the table for all days. So for example if article A has stock values for 16.01.2019 and 17.01.2019 but not for 18.01.2019, I would get this error message? 

If so, any possibility to overcome it in the sence that if article SKU (in the table "KEY" is not found for the date, then the stock value for this SKU should be zero). 

Thank you sooo much @The_Coon for your time and support. I would have still be looking for the solution for my problem.  VERY APPRICIATED

[Anonymous · Accepted solution]

Hi @borism84,

Yes this happens when a date is missing. Best solution to this, and for a lot of other benefits, is to create a separate DateTable and to create a relationship between the two tables. Then put your dates from your datetable in the matrix and slicer, the problem should be resolved then.

Easiest way to do it would be to create a new table using CALENDARAUTO. This function creates a table with dates, starting at the first date found in your data, and ends at the last date from your data.

Glad to be of help! I've learned a lot form this community and enjoy giving something back.

[borism84]

Thanks a lot. 

You have helped me amazingly. Will now investigate more on Power BI. 

[Anonymous]

Hi @borism84,

I think the PARALLELPERIOD function can help you here. You can create a measure with this function which calculates the SKU stocklevel - (SKU stocklevel on day -1).

Hope you can work it out. If you'll need any more help, I'd need to know how you calculate the SKU stock level now, 

Koen

[borism84]

Hi Koen

Thanks a lot for your reply. The SKU stock level now is not calculated at the moment,  its a number we pull from the internal database. 

What I am trying to do is to show how the stock level per product is changing per day, ideally i would be interested to see in the table the percentage change between two previous dates which i define in the table. 

Lets say you have 

SKU   Date   

         1/1/19     1/2/19      1/3/19    1/4/19    Change

A1          5              5              7              8          +14%

A2          6               5             5              6           +20%

A3         3              4              4              3             -25%

A4         9               6             5             5               0%               

If I change the date filter and show only between 1/1/19 and 1/3/19, the table should calculate the percentage change only between the last two rows: 

SKU   Date   

         1/1/19     1/2/19      1/3/19      Change

A1          5              5              7          +40%

A2          6               5             5             0%

A3         3              4              4              0%

A4         9               6             5             -17%          

I can also provide the data file if its needed. Thanks a lot for the help

[Anonymous]

@borism84

Previousnew = var maxre = CALCULATE(MAX(Sheet1[revision]),ALL(Sheet1),VALUES(Sheet1[Project]))
return
CALCULATE(MAX(Sheet1[Budget]),FILTER(Sheet1,Sheet1[revision]=if((maxre-1)=0,maxre,maxre-1)))

this measure will give you the immediate previous value based on your selection.

change the measure according to your table. 

this might solve your problem.

Regards,

Ashok T.

[borism84]

Hi Ashok

@Anonymous

Thanks a lot for the formula. Looks great, but somehow I get an error, probably as I am not so familiar with advanced Dax expressions yet, I insert wrong sheets or table. 

This is the table: Key is product ID, stock is stock number and date. 

And other attributes such as size and color are directly related to key (product ID). 

Could you help me out? 

[Anonymous]

@borism84,

For the ParallelPeriod syntax, see below. The only problem is that this measure will show mutation for every day. Might be that @Anonymous's solution doesn't do this.

Diff Last Day = (
calculate(sumx(YOURTABLENAME[Stock])) -
calculate(sumx(YOURTABLENAME[Stock]),parallelperiod('YOURTABLENAME'[Date],-1,DAY))/
calculate(sumx(YOURTABLENAME[Stock]))

Koen

[borism84]

Hi @Anonymous

Thanks a lot for the detailed formula, however, the parallelperiod does not support a day. Only month, quarter or year. I am getting the following error message

"A DAY interval was specified in the call to function Parallelperiod. This is not supported". 

Any suggestions? Thanks a lot

parallelperiod('YOURTABLENAME'[Date],-1,DAY))/

[Anonymous · Accepted solution]

You're right. I was just testing/creating an example file and found out. Solution: use DATEADD (see formula below), this function works the about same, but does support DAY as interval. You'll could use Parallelperiod if you want to calculate the difference of say a average per month or year. 

Here the formula for date add and printscreen of the result, i'll also try to upload my test .pbix file.

Diff Last Day = (
calculate(sumx(Stock_Table;Stock_Table[Stock])) -
calculate(sumx(Stock_Table;Stock_Table[Stock]),DATEADD(Stock_Table[Date],-1,DAY)))/
calculate(sumx(Stock_Table;Stock_Table[Stock]))

Stock Yesterday = calculate(sumx(Stock_Table;Stock_Table[Stock]),DATEADD(Stock_Table[Date],-1,DAY))

[borism84]

Re: Percentage change for recent two rows shown in the table

 [ New ] 

44m ago - last edited 42m ago

Dear @The_Coon

Its absolutely great. Thank you sooo much for the example and the detailed formula. 

Just one more detail. 

When I have selected a date range of two days 16.01.2019 - 17.01.2019, the table is workling perfectly. 

But if I increase the date selection even by one day more, I get an error messsage that the dateadd function expects a contigious selection. 

Does it mean, that same SKUs (Key= article SKU) should be in the table for all days. So for example if article A has stock values for 16.01.2019 and 17.01.2019 but not for 18.01.2019, I would get this error message? 

If so, any possibility to overcome it in the sence that if article SKU (in the table "KEY" is not found for the date, then the stock value for this SKU should be zero). 

Thank you sooo much @The_Coon for your time and support. I would have still be looking for the solution for my problem.  VERY APPRICIATED

[borism84]

Dear @The_Coon

Its absolutely great. Thank you sooo much for the example and the detailed formula. 

Just one more detail. 

When I have selected a date range of two days 16.01.2019 - 17.01.2019, the table is workling perfectly. 

But if I increase the date selection even by one day more, I get an error messsage that the dateadd function expects a contigious selection. 

Does it mean, that same SKUs (Key= article SKU) should be in the table for all days. So for example if article A has stock values for 16.01.2019 and 17.01.2019 but not for 18.01.2019, I would get this error message? 

If so, any possibility to overcome it in the sence that if article SKU (in the table "KEY" is not found for the date, then the stock value for this SKU should be zero). 

Thank you sooo much @The_Coon for your time and support. I would have still be looking for the solution for my problem.  VERY APPRICIATED

[Anonymous]

@Anonymous,

If in data, we dont have a previous date record? the what will the output be?

Regards,

Ashok T.

[Anonymous]

Hey @Anonymous, You'll probably get Inf. back. Because the (something minus the sum of nothing) divided by sum of nothing equeals infinity.

But thats only if the previous record doesn't exists in the data. If the data is filtered out, like if you only show the date of today, Parallelperiod will ignore the filter and the measure will show the % difference.