Contract Value where Contracts can span multiple years

Power BI
1

Thinking out loud here, open to ideas, feedback and comments!

I have a table of Contracts data that contains Account #, Contract #, Contract Value, Start Date and Expiration Date. I have another table of work orders for said customers. Looking to compare Contract Value for the year to work order value for the year. The issue I have is the contracts can span 6 months, 12 months, 450 months, etc. So if I have a contract that spans 5/17/2016 - 6/17/2025, with a single overall value associated, how can I display just the value for the past 12 months? The quick and dirty I think would work would be adding a Months column to the pull (Datediff( MONTH, contract.startdate, contract.expirationdate) AS ‘MonthsinContract’.

From there I could divide my total by months and and get the monthly value. Multiply by 12 to get full year value. This will have its own limitations though. Any thoughts on how to attack this or am I going to be limited based on the way the data is presented as an overall value instead of a monthly value with a term? Sample contract data attached EDNA Contracts.xlsx (56.3 KB)

2

I think want you would need here is some sort of allocation methodology for the contracts.

I would probably take the full amount and then divide it by the number of days in the contracts. That would give you a daily allocation figure per contract.

You could then use this number to span across each day of the contract.

This give you a lot of flexibility in how you can showcase it when you have some number at a daily granularity. You can create cumulative totals much easier which seems like would be the best way to showcase this.

As the table isn’t that big. You can probably created a calculated column for the daily allocation.

image
image840×521 29.1 KB

To then actually allocate it you’ll want to utilise a technique similar to this one.

See what you think.

3

Thanks Sam I’ll take a look at that video! So far I have the following which appears to give the results I am after:

Measure 2 = 
VAR Rate = CALCULATE( DIVIDE( [Contract Value], [Contracts Months Term], 0 ), USERELATIONSHIP( Dates[Date], 'Contracts'[ExpirationDate] ) )

RETURN 
CALCULATE( SUMX( VALUES( Dates[Date] ),
IF( AND( Dates[Date] >= STARTOFYEAR( Dates[Date] ), [Contracts Months Term] >= 12 ), CALCULATE( Rate * 12 ),
        IF( AND( Dates[Date] >= STARTOFYEAR( Dates[Date] ), [Contracts Months Term] < 12), CALCULATE( Rate * [Contracts Months Term] ), BLANK() ))), USERELATIONSHIP( Dates[Date], 'Contracts'[ExpirationDate] ))
4

Nice one.

I think we are thinking of similar ideas.

Maybe the cumulative total might be difficult with this one, I’m not 100% sure.

Looks good overall though.