cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
borism84 Frequent Visitor
Frequent Visitor

Percentage change for recent two rows shown in the table

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

 

Capture.GIF

2 ACCEPTED SOLUTIONS

Accepted Solutions
The_Coon Regular Visitor
Regular Visitor

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

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.image.png

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))
The_Coon Regular Visitor
Regular Visitor

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

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.

14 REPLIES 14
The_Coon Regular Visitor
Regular Visitor

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

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 Frequent Visitor
Frequent Visitor

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

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

Highlighted
Ashokt0025 Frequent Visitor
Frequent Visitor

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

@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 Frequent Visitor
Frequent Visitor

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

Hi Ashok

@Ashokt0025

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? 

Capture2.GIF

 

The_Coon Regular Visitor
Regular Visitor

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

@borism84,

 

For the ParallelPeriod syntax, see below. The only problem is that this measure will show mutation for every day. Might be that @Ashokt0025'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

Ashokt0025 Frequent Visitor
Frequent Visitor

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

@The_Coon,

 

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

 

Regards,

Ashok T.

The_Coon Regular Visitor
Regular Visitor

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

Hey @Ashokt0025, 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.

borism84 Frequent Visitor
Frequent Visitor

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

Hi @The_Coon

 

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))/

 

The_Coon Regular Visitor
Regular Visitor

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

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.image.png

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