Prior Day Calculation

[JEB85]

Hi,

Is there a way in powerpivot to calculate a change in the number jobs from the prior working day. My desired result is shown in the Change on Last Day column:


[TABLE="width: 321"]
<TBODY>[TR]
[TD]Date</SPAN>[/TD]
[TD]Number of Jobs</SPAN>[/TD]
[TD]Change on Last Day</SPAN>[/TD]
[/TR]
[TR]
[TD]01/08/13</SPAN>[/TD]
[TD="align: right"]1831</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05/08/13</SPAN>[/TD]
[TD="align: right"]1802</SPAN>[/TD]
[TD="align: right"]-29</SPAN>[/TD]
[/TR]
[TR]
[TD]06/08/13</SPAN>[/TD]
[TD="align: right"]1888</SPAN>[/TD]
[TD="align: right"]86</SPAN>[/TD]
[/TR]
[TR]
[TD]07/08/13</SPAN>[/TD]
[TD="align: right"]1882</SPAN>[/TD]
[TD="align: right"]-6</SPAN>[/TD]
[/TR]
[TR]
[TD]08/08/13</SPAN>[/TD]
[TD="align: right"]1909</SPAN>[/TD]
[TD="align: right"]27</SPAN>[/TD]
[/TR]
[TR]
[TD]09/08/13</SPAN>[/TD]
[TD="align: right"]1825</SPAN>[/TD]
[TD="align: right"]-84</SPAN>[/TD]
[/TR]
[TR]
[TD]12/08/13</SPAN>[/TD]
[TD="align: right"]1828</SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[/TR]
[TR]
[TD]13/08/13</SPAN>[/TD]
[TD="align: right"]2186</SPAN>[/TD]
[TD="align: right"]358</SPAN>[/TD]
[/TR]
[TR]
[TD]14/08/13</SPAN>[/TD]
[TD="align: right"]2376</SPAN>[/TD]
[TD="align: right"]190</SPAN>[/TD]
[/TR]
[TR]
[TD]15/08/13</SPAN>[/TD]
[TD="align: right"]2457</SPAN>[/TD]
[TD="align: right"]81</SPAN>[/TD]
[/TR]
[TR]
[TD]16/08/13</SPAN>[/TD]
[TD="align: right"]2329</SPAN>[/TD]
[TD="align: right"]-128</SPAN>[/TD]
[/TR]
[TR]
[TD]19/08/13</SPAN>[/TD]
[TD="align: right"]2257</SPAN>[/TD]
[TD="align: right"]-72</SPAN>[/TD]
[/TR]
[TR]
[TD]20/08/13</SPAN>[/TD]
[TD="align: right"]2384</SPAN>[/TD]
[TD="align: right"]127</SPAN>[/TD]
[/TR]
[TR]
[TD]21/08/13</SPAN>[/TD]
[TD="align: right"]2371</SPAN>[/TD]
[TD="align: right"]-13</SPAN>[/TD]
[/TR]
[TR]
[TD]22/08/13</SPAN>[/TD]
[TD="align: right"]2264</SPAN>[/TD]
[TD="align: right"]-107</SPAN>[/TD]
[/TR]
[TR]
[TD]23/08/13</SPAN>[/TD]
[TD="align: right"]2366</SPAN>[/TD]
[TD="align: right"]102</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

Thanks

 

[MD610]

Without a separate date table, this could be done with 2 calculated columns. 1 to rank the dates and the second to lookup the value of the previous rank.

Call this one Rank:

=RANKX(TableName, TableName[Date],,1,DENSE)

Call this whatever you want:

=TableName[Number of Jobs] - LOOKUPVALUE(TableName[Number of Jobs],TableName[Rank], TableName[Rank]-1)

 

[JEB85]

I have a dates table if that makes things easier?

Will give your solution a go tomorrow.

Thanks