Forum Discussion
Forecast sales when there are multiple invoice dates
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]))
Resolver II 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.
