Hello All,
I'm working on a pipeline revenue. Got the following tables
MasterCalendar
- Date
- Weeknr
- Year
- WeeknrYear
Projects
- Projectnr
- TotalAmount
Invoices Schedule
- Projectnr
- Date
- Invoicenr
- Amount
- Percentage
About the invoices Schedule the amount could be 0 with a percentage (50%) so the invoices will be TotalAmount * Percentage. At last every Project has an invoice with Amount 0 and Percentage 0 named ClosingInvoice. This is for the extra work. TotalAmount will be updated but now the invoices Schedule must calculate what the amount of the ClosingInvoice will be.
Example:
Projects
| Projects | TotalAmount |
|---|
| X | 100.000 |
| Y | 50.000 |
Project X was 90.000 but has 10.000 extra work
Invoices Schedule
| Projectnr | Date | Invoicenr | Amount | Percentage |
|---|
| X | 1-1 | 1 | 50.000 | |
| X | 2-1 | 2 | 40.000 | |
| X | 3-1 | 3 | 0 | 0 |
| Y | 1-1 | 4 | | 50% |
| Y | 3-1 | 5 | 0 | 0 |
The ClosingInvoice could be calculated with TotalAmount - amount already been invoiced. I use sum(TOTAL <Projectnr> Amount)
What I need is as follows
| Date | Revenue (correct) | Revenues (wrong) |
|---|
| 1-1 | 75.000 | 75.000 |
| 2-1 | 40.000 | 40.000 |
| 3-1 | 35.000 | -80.000 |
3-1 Correct:
100.000 - (50.000 + 40.000)
50.000 - (25000)
3-1 Wrong:
100.000 - (50.000 + 40.000 + 25.000)
50.000 - (50.000 + 40.000 + 25.000)
When I select one contract everything is working fine
