Forum Discussion
Forecast sales when there are multiple invoice dates
Hi - I am trying to build a sales forecasting dashboard for a consultancy business. They would like to be able to see monthly revenue based on invoiced date. Normally, projects are invoiced at the end of a project, so summing invoiced amount on completion date is simple enough.
However, I am stumbling on projects where a project is invoiced 50% upfront and 50% on completion.
| Date Entered | Client | Service Line | Project | Status | Min Budget | Max Budget | Weighting | % Invoiced upfront | Start Date | Completion Date |
| 12/04/2023 | XYZ | IT Transformation | Lightning | Completed | £ 6,000 | 100% | 0% | 01/05/2023 | 31/05/2023 | |
| 12/03/2023 | ABC | Code Review | Zeus | Scheduled | £ 12,500 | £ 15,000 | 50% | 50% | 01/09/2023 | 12/10/2023 |
The above is how the data is entered into an excel file. Ideally, they'd like a bar chart that can be drilled through to a monthly, quarterly or annual view.
The above table shows two examples: a project that is 100% invoiced at the end and thus should show £6000 in May 2023. The second project is invoiced 50% upfront and 50% at the end, so should show £6,250 in September 2023 and £6,250 in October 2023.
How do I best go about this?
I have realised this approach will only work properly if all your dimensions are in separate tables. If you have a one-table model and don't fancy sweeping all your dimensions into separate tables, you could, in Power Query:
1. Add an [Invoice Date] column which is just equal to [Completion Date]
2. Add an [Invoice Value] column which is [Weighting] * [Min Budget]
3. Add a [Row Type] column which is ="Normal" (so it's the same for every row).
4. Create a new table in Power Query by referencing your existing table. Call it 'upfront payments'. Disable data-load for both tables so far.
5. In 'upfront payments', delete the [Invoice Date], [Invoice Value], and [Row Type] columns
6. In 'upfront payments', add an [Invoice Date] column which is just equal to [Start Date]
7. In 'upfront payments', add an [Invoice Value] column which is (1-[Weighting]) * [Min Budget]
8. In 'upfront payments', add a [Row Type] column which is ="Upfront Payment"
9. Create a new table by appending those two tables together. This is your new data. Save & close.
10. Add a report-level filter on [Row Type]="Normal"
11. Your revenue measure is now:
Revenue = CALCULATE(SUM('yourtablename'[Invoice Value]), ALL('yourtablename'[Row Type]))
3 Replies
whitchResolver I
I have realised this approach will only work properly if all your dimensions are in separate tables. If you have a one-table model and don't fancy sweeping all your dimensions into separate tables, you could, in Power Query:
1. Add an [Invoice Date] column which is just equal to [Completion Date]
2. Add an [Invoice Value] column which is [Weighting] * [Min Budget]
3. Add a [Row Type] column which is ="Normal" (so it's the same for every row).
4. Create a new table in Power Query by referencing your existing table. Call it 'upfront payments'. Disable data-load for both tables so far.
5. In 'upfront payments', delete the [Invoice Date], [Invoice Value], and [Row Type] columns
6. In 'upfront payments', add an [Invoice Date] column which is just equal to [Start Date]
7. In 'upfront payments', add an [Invoice Value] column which is (1-[Weighting]) * [Min Budget]
8. In 'upfront payments', add a [Row Type] column which is ="Upfront Payment"
9. Create a new table by appending those two tables together. This is your new data. Save & close.
10. Add a report-level filter on [Row Type]="Normal"
11. Your revenue measure is now:
Revenue = CALCULATE(SUM('yourtablename'[Invoice Value]), ALL('yourtablename'[Row Type]))
Thank you so much - this is perfect. I totally forgot about appending tables - the ideal manual solution would have been for a salesperson to add two rows to each project (one start date and one end date) with the invoiced value but this does the same thing without them having to manually add a second row.
- whitch
My approach would be to create an 'upfront payments' table. In overview:
1. Create a new table in Power Query by referencing your existing table. Keep Client/Service Line/Project/Status/Start Date. Calculate a new "Upfront Value" column from the budgets & weighting. Save & load.
2. If you have a date table (or any other dimension tables) make sure they are joined to both your existing table and this new table in modelling. If you don't have a date table, make one, and join it to [Completion date] in your original table and [Start Date] in your new 'upfront payments' table.
3. Create a [Revenue] measure which is the final payments from your existing data plus the "Upfront Value" from your new table.
Let me know if you want more detail on any step. I have assumed you're fairly familiar with PBI already.
