Forward looking revenue/margin projection for existing contracts

[jeffshields]

Hello and thanks in advance for any assistance,

I feel like this may be fairly straightforward as the formualtions don't seem that complex but I'm not sure how to go about doing in PBI. I have some contract/agreement data that a client would like to see projected out in the future based on current revenue. There are monthly, quarterly and annual contracts in the mix. The client would like to see an aggregate total of all revenue and contract margin (two separate visualizations) going out in the near future; say for the next year, broken out by month and agreement type.

Luckily the table structure and values are all fairly simple:

I have an agreements table which houses the [total billing amount] and [contract end date] and [agreement type]. That agreements table is tied to an agreeement_additions table via an agreement_rec_id; the agreement_additions table houses the total cost of the additions associated with the agreement. I would need to get each agreement margin by totalling the addition cost of all active additions per agreement and subtracting that from the total billing amount. EG for each agreement: [total_billing_amount] - (sum[addition_cost]) = margin for that month.

I need to be able to show the aggregate total revenue per month both current and projected out (1st visualization) and total margin per month both current and projected out (2nd visualization), both with the ability to break out by agreeement type.

There already exists a seperate date table that I can use for measures, etc...

I'm thinking I'll need a new measure for margin and then somehow tie the both the margin and revenue to a monthly date calculation that would end when the agreement end date hits. Does that sound right? Beyond that, I'm not sure how to get the sum total of all agreement revenue/margin for current/past months as well as a few months/a year into the future.

Any help would be greatly appreciated. Please let me know if more info is needed or if any points need clarifying. Thank you again in advance!

[Kedar_Pande]

Your approach seems right.

Total Revenue = 
CALCULATE(
SUM(Agreements[total_billing_amount]),
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH),
'Date'[Date] <= Agreements[contract_end_date]
)

Total Margin = 
CALCULATE(
SUM(Agreements[total_billing_amount]) - SUM(agreement_additions[addition_cost]),
'Date'[Date] <= Agreements[contract_end_date]
)

Projected Revenue = 
CALCULATE(
[Total Revenue],
FILTER('Date', 'Date'[Date] > TODAY() && 'Date'[Date] <= Agreements[contract_end_date])
)

Projected Margin = 
CALCULATE(
[Total Margin],
FILTER('Date', 'Date'[Date] > TODAY() && 'Date'[Date] <= Agreements[contract_end_date])
)

[jeffshields]

Hi! So I attempted to create the Total Revenue measure as explained above, but am getting an error on the expression. I can apparentely only uses fields from the same table when creating table filter expressions. I think the comparison between date and agreement enddate is the issue.

The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression.

Any thoughts?

[jeffshields]

Thank you for the verification and confirmation that my logic is sound! I'll work on getting these formulas into the report and let you know if I have any future questions. Much appreciated my friend!

[Kedar_Pande]

Your Kudos/Likes are much appreciated!
If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Regards,
Kedar Pande
www.linkedin.com/in/kedar-pande

[rubayatyasmin]

Hi, @jeffshields 

Try creating seperate measures for revenue and cost then subtract them to get margin. For example, 
Measure 1: Total Revenue = SUM(Agreements[total billing amount])
Measure 2: Total Cost = SUM(agreement_additions[addition_cost])
Measure 3: Margin = [Total Revenue] - [Total Cost]

to project the data out for the future and past/current months, you’ll need to use your date table effectively. make sure your date table is marked as a date table in Power BI and has a relationship with the Agreements table through the [contract end date] or another relevant date col. 

For current and past data you should be good to go. Just use date slicer. Again correct relationship with the calender table is very important. 

as for future projections I suppose you are searching for the ALL dax func. For example, 
for the revenue use something like below, 
Projected Revenue = CALCULATE(
[Total Revenue],
FILTER(
ALL('Date'[Date]),
'Date'[Date] <= MAX(Agreements[contract end date])
)
)

As for visualization: you mentioned 2 seperate viz, so try creating monthly revenue and monthly margin viz. use col,bar or line chart. see what fits. I think bar chart will go fine as you want to show agreement type as well. 

[jeffshields]

Thank you for the feedback and advice! I'll work on this in the coming days and let you know if I have any questions. Your help is much appreciated!