1 Reply Latest reply on Jul 22, 2014 12:29 PM by philmodjunk

# Make a monthly report which includes previous month results

### Title

Make a monthly report which includes previous month results

### Post

Hi,

my company rent containers to customers. Each month we have to do a balance of containers (returned or rented) for each customer. I have a customer table and another table for containers.

I would like to display a monthly report which include the previous month balance at the top of the layout.

Example for the month of july:

balance from june: returned 5 containers, rented 4 containers, so balance is 4-5 = -1.

then

01/07/14, 1 returned, 0 rented

02/07/14, 0 returned, 0 rented

etc.

31/07/14, 0 returned, 4 rented

balance is 4-1 = 3

do the total balance at the bottom: -1 (june) + 3 (july) = 2

The difficulty is to include the balance of the previous month in the calculation of the current month.

I hope somebody can help,

PT

• ###### 1. Re: Make a monthly report which includes previous month results

You can use a relationship that refers to all related records with a date one month less than either the current record's date or the current calendar date from your computer's system clock.

The trick is to use a calculation that returns the same value for all records of the same month and year.

DateField - Day ( DateField ) + 1

Will return the date for the first day of the month for all dates in that month.

Date ( Month ( DateField ) - 1 ; 1 ; Year ( DateField ) )

Will return a matching date for the previous month.

Substitute Get ( CurrentDate ) for DateField in the last calculation to get a matching value for the month preceding the current month as shown on your system clock.