How to calculate percentage change from previous month?

[Vajid Idrees]

Hi, I am creating a report which needs to calculate the %change from the previous month. I have tried using a nested query which tries to take the value from the previous month and then the current month to get the % change. The issue is the output is in the wrong place. Any help will be much appreciated.
Thank you

SELECT [Data Research].[Date of Request] AS [Month], [Data Research].[Number of Requests] AS [Total Requests], [Data Research].[Number of Items Requested] AS [Total Items], [Data Research].[Number added to DCS] AS [Items Found], Format(([Number added to DCS]/[Number of Items Requested])*100,"Fixed") AS [% Found], [Data Research].[DCS Assigned] AS [Number Migrated], [Data Research].[DCS Backlog] AS Backlog, Format(((((Select [Data Research].[DCS Backlog] FROM [Data Research] WHERE Year([Date of Request])* 12 + DatePart("m", [Date of Request]) = Year(Date())* 12 + DatePart("m", Date()) - 1)/[Data Research].[DCS Backlog])-1)*100), "fixed") AS [Backlog % increase/decrease]
FROM [Data Research];
 

[Rabbit]

What do you mean the output is in the wrong place? Just change the place.

[Vajid Idrees]

For example, I want to calculate the %change from jan to feb, this should go in the feb record, but the way the above query works, it puts the answer in the jan record

[Rabbit]

Can you post some sample data and the expected results?

[Vajid Idrees]

Sorry for the delay in replying Rabbit, been put on other projects by management, but now they want me to finish off this one.
Back to your question

Data looks like this

Date of Request    Number of Requests    Number of Items Requested    Number added to DCS    DCS Backlog    DCS Assigned    Requests added to wishlist
2010/06    12    45    14            
2010/07    20    42    18            
2010/08    21    24    10            
2010/09    31    41    14            
2010/10    24    40    14    45    12    
2010/11    46    80    20    37    28    12
2010/12    16    28    15    41    10    10
2011/01    30    61    10    35    17    11
2011/02    18    24    10    23    21    12
 

I need the output to look like this

Month    Total Requests    Total Items    Items Found    % Found    Number Migrated    Backlog    Backlog % increase/decrease
2011/01    30    61    10    16.39    17    35    
2011/02    18    24    10    41.67    21    23    
 

The Backlog% will be the % difference from Jan to Feb, in the Feb row.
I hope that makes sense, and thank you for your help.

[Rabbit]

You'll need to use a subquery to return the prior month's backlog so that you can use it in the calculation. Assuming the formula you're using is (CurrentRowBacklog - PriorMonthBacklog) / PriorMonthBacklog, you will need to use the subquery twice to return the prior month's backlog.