cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FryBINub
Regular Visitor

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

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. 

 

2 ACCEPTED SOLUTIONS

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
image.png

View solution in original post

@FryBINub 

 

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])

 

 

picture 1.PNG

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]))

 

 

picture 2.PNG

 

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

picture 4.PNG

 

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]))

 

 

picture 3.PNG

 

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

  

picture 5.PNG

 

Best regards

Mikelytics

 

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

 

Appreciate your Kudos.

View solution in original post

10 REPLIES 10
Mikelytics
Resolver III
Resolver III

Hi @FryBINub 

 

Please try the following solution.

 

You dont need a relationship between the tables

picture 1.PNG

 

Calulcate the revenue per day in the contract table with:

 

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

 

picture 2.PNG

 

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]))

 

picture 3.PNG

 

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

 

picture 4.PNG

 

 and also group it by your attributes from the date table

picture %.PNG

 

I hope this helps.

 

Best regards

Mikelytics

 

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

 

Appreciate your Kudos.

  

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?

@FryBINub 

 

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

@FryBINub 

 

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])

 

 

picture 1.PNG

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]))

 

 

picture 2.PNG

 

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

picture 4.PNG

 

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]))

 

 

picture 3.PNG

 

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

  

picture 5.PNG

 

Best regards

Mikelytics

 

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

 

Appreciate your Kudos.

View solution in original post

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
Resident Rockstar
Resident Rockstar

@FryBINubcan 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


New Animated Dashboard: Sales Calendar


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
image.png

View solution in original post

smpa01
Resident Rockstar
Resident Rockstar

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


New Animated Dashboard: Sales Calendar


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).

FryBINub
Regular Visitor

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! 🙂

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors