Forum Discussion
Calculate Monthly Total between two dates
We recieve payment from customers on a reocurring basis for the duration of a contract. I don't have a Date table associated to the fact table. Is there a way to calculate monthly revenenue so it is viewable by Year, and Month? We have a measure built and we can view by YearMonth but it isn't collapsible to just year.
Is it easiest to import the contract closed date and connect a date table to that column?
Below I posted a sample source table and a sample matrix output.
Table
| Sales # | Name | Contract Value | Start Date | End Date | Rev Monthly | Status |
| 3046906 | Cust 1 | $164,132 | 7/01/2020 | 6/30/2021 | $13,678 | Active |
| 3062669 | Cust 2 | $426262 | 8/01/2018 | 7/31/2020 | $7,104 | Active |
Output Month and Year:
| Customer | Sales Number | Project | Jun 20 | Jul 20 | Aug 20 | Sep 20 | Oct 20 | Nov 20 | etc |
| 1 | 3046906 | x | $13,678 | $13,678 | $13,678 | $13,678 | $13,678 | ... | |
| 2 | 3062669 | x | $7,104 | $7,104 | ... |
Output Year:
| Customer | Sales Number | Project | 2018 | 2019 | 2020 | etc |
| 1 | 3046906 | x | $82,068 | ... | ||
| 2 | 3062669 | x | $35,520 | $85,248 | $49,728 | ... |
Hi Anonymous
You do want a date table, but because you don't have a transaction table per se (you just show monthly revenue as a "component" of the contract), we can create a transaction table that can then get the output you seek.
1) Create the date table (CALENDARAUTO() works fine). Include at least Month and Year as columns.
2) Create a "transaction" table using CROSSJOIN (we will also put in the restrictions here of making sure the revenue entries are between the start and end date)
MonthlyRev = FILTER ( CROSSJOIN ( FILTER ( DateTab, DAY ( DateTab[Date] ) = 1 ), SUMMARIZE ( Contract, Contract[Sales #], Contract[Name], Contract[Rev Monthly ], Contract[Start Date], Contract[End Date] ) ), [Date] >= [Start Date] && [Date] < [End Date] )3) Make the matrix with Year, Month as the columns, Name as the rows, Rev Monthly as the values
Yearly rollup:
Drill down to month:
Hope this helps
David
3 Replies
Hi Anonymous
You do want a date table, but because you don't have a transaction table per se (you just show monthly revenue as a "component" of the contract), we can create a transaction table that can then get the output you seek.
1) Create the date table (CALENDARAUTO() works fine). Include at least Month and Year as columns.
2) Create a "transaction" table using CROSSJOIN (we will also put in the restrictions here of making sure the revenue entries are between the start and end date)
MonthlyRev = FILTER ( CROSSJOIN ( FILTER ( DateTab, DAY ( DateTab[Date] ) = 1 ), SUMMARIZE ( Contract, Contract[Sales #], Contract[Name], Contract[Rev Monthly ], Contract[Start Date], Contract[End Date] ) ), [Date] >= [Start Date] && [Date] < [End Date] )3) Make the matrix with Year, Month as the columns, Name as the rows, Rev Monthly as the values
Yearly rollup:
Drill down to month:
Hope this helps
David
Anonymous
I was thinking on this one over night because I wanted to figure out a solution using a measure and was able to get there. I know dedelman_clng was able to solve it with a table but wanted to offer this as well so you could see it.
Monthly Rev Measure = CALCULATE ( SUMX ( VALUES ( Dates[Month Year] ), VAR _FirstDate = FIRSTDATE ( Dates[Date] ) VAR _LastDate = LASTDATE ( Dates[Date] ) RETURN CALCULATE ( SUM( Contracts[Rev Monthly] ), Contracts[Start Date] <= _LastDate, Contracts[End Date] >= _FirstDate ) ) )dedelman_clng This is exactly what I needed, thank you sir. Worked like a charm.
