Showing revenue spread out over the duration (months) of a contract

[Anonymous]

Hi all,

I am working to show a booked revenue spread out over teh course of the contract by month.

Example: 
Contract awarded for $300. 

Contract start date is 1/15/2021

Contract end date is 4/12/2021

How do I show the revenue generated by month?

Right now I can get it to populate $300 for each month Jan, Feb, Mar, Apr with a grand total of $300. I also divided the revenue by day and can populate the months @ $100/month (Jan, Feb, Mar, Apr) with a grand total of $100. 

I have a date table with FullDate, Year, Month, Month Name, Week of Year, Day of Month, Day of Week, Day of Year, and Day Name colums. 

The other table consists of Job Name, Total Revenue, Duration (Months), Duration (days), Start Date, End Date

Any help would be greatly appreciated. 

[Anonymous · Accepted solution]

smpa01,

It would be a chart that looked like this with the total revenue distributed through the active months and totaled at the end of the column and end of the row.

Right now the data I have is Job, Total Revenue, Start Date, End Date, Duration (months), Duration (days)

Job | Jan | Feb | Mar | ..... Total

[Mikelytics]

Hi @Anonymous 

Please try the following solution.

You dont need a relationship between the tables

Calulcate the revenue per day in the contract table with:

revenue per day = Contracts[Contract awarded]/(Contracts[end date]-Contracts[start date])

then calulcate the total revenue per day in the date table with the following measure.

total revenue from contracts = 
CALCULATE(SUM(Contracts[revenue per day]),
FILTER(ALL(Contracts),
'Date'[Date]>= Contracts[start date] &&
'Date'[Date]<= Contracts[end date]))

 

you can now identify the revenue per day using the date table.

 and also group it by your attributes from the date table

I hope this helps.

Best regards

Mikelytics

Did I solve your request? Please mark my post as solution.

Appreciate your Kudos.

  

[Anonymous]

Mikelytics,

I think this will help. If I don't want to show the revenue per contract, do I need that Total Revenue per day in the date table?

[Mikelytics]

@Anonymous 

if value per month or day without contract is fine I think this is still the best solution. THe date table helps to make the calulcation much easier. 

If you would like to have a solution with contract break down I have also an approach which I can share in 5 min. in this case you have a separate table with a cross join so that you dont have the values in the date table.

But without contract break down the approach above is the best which I am aware of.

Best regards

Mikelytics

Did I solve you request? Please mark my post as solution.

Appreciate your Kudos.

Best regards

Michael

[Mikelytics · Accepted solution]

@Anonymous 

Please find below the second appraoch:

You have the same date set and still the calulcated column for revenue by day in the contract table

revenue per day = Contracts[Contract awarded]/(Contracts[end date]-Contracts[start date])

 

Now you create a calculcated column with the following formula using the date column from date table and the contract column from contract table.

Cross Contracts and Dates = CROSSJOIN(SELECTCOLUMNS('Date',"Date",'Date'[Date]),SELECTCOLUMNS(Contracts,"Contract",Contracts[Contract ID]))

now you create a relation between the date table and the new created calulcated table (cross...) with one:many

now you go into the cross table and create the following caluclated column

revenue = 
CALCULATE(SUM(Contracts[revenue per day]),
FILTER(ALL(Contracts),
'Cross Contracts and Dates'[Date]>= Contracts[start date] &&
'Cross Contracts and Dates'[Date]<= Contracts[end date] &&
'Cross Contracts and Dates'[Contract]=Contracts[Contract ID]))

Now using the cross table and the date table you can create the following matrix.

  

Best regards

Mikelytics

Did I solve your request? Please mark my post as solution.

Appreciate your Kudos.

 

[Anonymous]

MIke, 

This is brilliant. Thank you so much!

I have 1 issue. It's always adding 1 additional day to the total revenue. So if it's a $300 contract over 3 days, it's giving me $400. I just subtracted 1 to the Revenue/Day calc, so it's Rev/Day = Rev/(Contract End Date)-(Contract Start Date -1) and it works perfectly.

Thank you again!

[smpa01]

@Anonymouscan you please share your expected output for the following dataset

Example:
Contract awarded for $300.

Contract start date is 1/15/2021

Contract end date is 4/12/2021

[Anonymous · Accepted solution]

smpa01,

It would be a chart that looked like this with the total revenue distributed through the active months and totaled at the end of the column and end of the row.

Right now the data I have is Job, Total Revenue, Start Date, End Date, Duration (months), Duration (days)

Job | Jan | Feb | Mar | ..... Total

[smpa01]

@Anonymousagian hard to imagine without you providing a sample of the output that you expect, can't help unless you help me to help you

[Anonymous]

Sorry, check my response again. the forum wasn't allowing me to post a table.

In my photo, the first column is cut off but that would be the Job Name.

Right now, it's using the entire total for the job and populating over the active months. I want it to divide the revenue amongst the months (even better would be to show partial revenue for months that we aren't active for the entirety of the month).

[Anonymous]

Is this an impossible task? I'm not the best with BI, and I've spent too many hours already trying to figure this one out. 

Put me out of my misery if it's not possible, please! 🙂