Calculate Monthly Total between two dates

[Anonymous]

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

[dedelman_clng · Accepted solution]

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

[jdbuchanan71]

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

[Anonymous]

@dedelman_clng This is exactly what I needed, thank you sir.  Worked like a charm.