data of previous week

[ahmedismailfourtex]

hello,
I have sales table like this.

product	week	sales
x	1	10
y	1	15
z	1	20
x	2	20
y	2	10
z	2	15

<tbody>
</tbody>

and I need to have column of sales in previous week, so I can calculate the variance of any specific product. I am using Power BI.
can I get help ?
Thanks

 

[anvg]

Hi
Try with calculated column

=CALCULATE(VALUES(Table1[sales]),FILTER(ALL(Table1[week],Table1[sales]),EARLIER('Table1'[week])=('Table1'[week]+1)))

Regards,

 

[ahmedismailfourtex]

Hi
Try with calculated column

=CALCULATE(VALUES(Table1[sales]),FILTER(ALL(Table1[week],Table1[sales]),EARLIER('Table1'[week])=('Table1'[week]+1)))

Regards,

Thank you for respond, when I am calculating variance "diff. between current and previous" in calculated column it gives circular error !, I need that variance in a column to calculate average variance.
Any idea how to deal with it ?
thanks

 

[anvg]

Hi

I need that variance in a column to calculate average variance.

Could you upload your example to dropbox or other place for testing?
Regards,

 

[ahmedismailfourtex]

Hi

Could you upload your example to dropbox or other place for testing?
Regards,

here you are,
solution by excel in next page
https://1drv.ms/x/s!Am-ng3dX8ExojEPO1bNQQ--I0WQ7

 

[anvg]

Hi
But I do not understand a structure of "Solution in excel". For "ANDY /W" article for 13 week you have change -2408, but total sale for 12 and 13 is 0. How do you calculate that change value? Do I understand you rightly that average is calculated for all non-blank values of change?

 

[anvg]

As a variant Zippyshare.com - Weekly%20DB.xlsx

 

[ahmedismailfourtex]

Hi
But I do not understand a structure of "Solution in excel". For "ANDY /W" article for 13 week you have change -2408, but total sale for 12 and 13 is 0. How do you calculate that change value? Do I understand you rightly that average is calculated for all non-blank values of change?

Sorry for delay and sorry if it disturbed you, this value of change is for stock (all fabric) so its logic to see it in minus.