Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
@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.
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.
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?
@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
@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.
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!
@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
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
@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
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).
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! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |