Calculate Difference and Percent Change from Previous Week Quantity by Item Description

[MADness449]

I am trying to create a column that has the difference in quantity from one week to the next by item description. You can see the formula I have tried below, however it is returning an error.  I know the "prevweek" variable is working as expected because when I return that instead of "result" it populates all values 7 days prior. I suspect this may have to do with how I am using the LOOKUPVALUE function to find the previous week's instance of the item description. I am also looking to create another column with the % change value from the previous week to the following week by item description as well. Would appreciate any help!

[Anonymous · Accepted solution]

Hi @MADness449 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create calculated columns.

1 week ago = 
var currentweek=Main[date]
var preweek=Main[date]-7
var currentprod=Main[Item Description]
var currentqty=Main[total field oh]
var prevqty=CALCULATE(SUM('Main'[total field oh]),FILTER('Main','Main'[date]=preweek  && 'Main'[Item Description]=currentprod))
return prevqty

% change = 
IF('Main'[1 week ago]=BLANK(),0,DIVIDE(('Main'[total field oh]-'Main'[1 week ago]),'Main'[total field oh],0))

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.