Cumulative Revenue using Billing Start and End Dates

[ChrisJC]

I have a data table for Licences with a linked date table:

The table has a billing start and billing end date for each row to determine the period where the licence is billed. If the end date is blank then it should be billable forever after the start date.

I have a measure for Revenue in Month (although it will work across any period drilled down to). This works fine based on my testing.

Revenue In Month = 
VAR MinDate = LASTDATE ( 'calc_date'[Date])
VAR MaxDate = MAX('calc_date'[Date])
RETURN
    CALCULATE(
        SUM( data_licences[PPL] ),
        ALLSELECTED( data_licences ),
			data_licences[cld_billingstartdate] <= MaxDate , 
			( data_licences[cld_billingenddate] > MinDate || ISBLANK(data_licences[cld_billingenddate]) )
    )

I need to extend this to add a cumulative revenue measure. I have tried both using my existing measure and starting from scratch but have had no success.

This attempt is trying to SUMX across the months in the date table but only returns for each month in the visual, same as the Revenue in Month measure:

Cumulative Revenue = 
VAR MinDate = MINX( ALLSELECTED( 'calc_date'[Date] ),'calc_date'[Date] )
VAR MaxDate = MAX( 'calc_date'[Date] )
VAR _mnth = SELECTEDVALUE('calc_date'[Year Month])
RETURN
    SUMX(
    	FILTER(
       	 	ALLSELECTED( calc_date[Year Month] ),
       	 	//MIN( calc_date[Date] ) >= MinDate && MAX( calc_date[Date] ) <= MaxDate
       	 	calc_date[Year Month] <= _mnth && MIN( calc_date[Date] ) >= MinDate
      	 	)            
        ,data_licences[Revenue In Month]
    )

This is an example of what I'm expecting:

	Revenue in Month	Cumulative Revenue
Apr	25	25
May	40	65
Jun	100	165
Jul	100	265
Aug	65	330

[v-jingzhang · Accepted solution]

Hi @ChrisJC 

I add an inactive relationship between two tables on calendar table [Date] column and data table [Date] column. And create two measures to get the results of Column H and Column I in your Excel file. In the second measure, I use a variable table to get the monthly results first and filter the variable table to get the ultimate cumulative result.

Cum Rev In Month = 
VAR __endOfMonth = MAX(calc_date[Date])
VAR __minDate = [MinDate]
VAR __in = CALCULATE(SUM(data_licences[PPL]),ALLSELECTED(calc_date),calc_date[Date]>=__minDate,calc_date[Date]<=__endOfMonth)
VAR __out = CALCULATE(SUM(data_licences[PPL]),ALL(data_licences),NOT(ISBLANK(data_licences[cld_billingenddate])),data_licences[cld_billingenddate]<=__endOfMonth,USERELATIONSHIP(calc_date[Date],data_licences[cld_billingenddate]))
RETURN
__in - __out

Cumulative Rev = 
VAR __endOfMonth = MAX(calc_date[Date])
// VAR __minDate = [MinDate]
VAR __table = SUMMARIZE(ALLSELECTED(calc_date),calc_date[Calendar Year],calc_date[Year Month],"Cum_Rev_In_Month",[Cum Rev In Month],"End_Of_Month",MAX(calc_date[Date]))
RETURN
SUMX(FILTER(__table,[End_Of_Month]<=__endOfMonth),[Cum_Rev_In_Month])

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[amitchandak]

@ChrisJC , try a measure like

Cumm Revenue In Month =
VAR MinDate = Minx (allselected('calc_date'), 'calc_date'[Date])
VAR MaxDate = MAX('calc_date'[Date])
RETURN
CALCULATE(
SUM( data_licences[PPL] ),
ALLSELECTED( data_licences ),
data_licences[cld_billingstartdate] <= MaxDate ,
( data_licences[cld_billingenddate] > MinDate || ISBLANK(data_licences[cld_billingenddate]) )
)

[ChrisJC]

@amitchandakI have worked out why the normal cumulative calculation doesn't work but am not able to fix it.

The normal calculation that you've provided relies on facts of transactions in this case.

Whereas my data table has a date period where each customer should have been billed along with the monthly price per licence (PPL).

Therefore, I need to SUMX over the months in the selected date period and calculate the SUM of the PPL figures for applicable licences in that month.

This comes close, however it appears to be multipling the correct result by the number of months up to the current month in the row context

Cumulative Revenue In Month (SUMX) = 
VAR StartDate = Minx ( allselected( 'calc_date' ), 'calc_date'[Date] )
VAR MinMonthDate = Min( 'calc_date'[Date] )
VAR MaxMonthDate = Max( 'calc_date'[Date] )
VAR _mnth = SELECTEDVALUE('calc_date'[Year Month])
RETURN
    SUMX(
        FILTER(
        	ALLSELECTED( calc_date[Year Month] ),
        	//MIN( calc_date[Date] ) >= MinDate && MAX( calc_date[Date] ) <= MaxDate
        	calc_date[Year Month] <= _mnth && MIN( calc_date[Date] ) >= StartDate
        ),
        VAR md = MAX( 'calc_date'[Date] )
        RETURN
        CALCULATE(
            SUM( data_licences[PPL] )
            ,ALLSELECTED( data_licences ),
            data_licences[cld_billingstartdate] <=  md,
            data_licences[cld_billingstartdate] >= StartDate,
            ( data_licences[cld_billingenddate] >=  md || ISBLANK(data_licences[cld_billingenddate]) )
        )
    )

:

[v-jingzhang]

Hi @ChrisJC 

Can you provide some sample data and expected result so that we can use it to test the measures? See https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

Will PPL figures differ in different periods?

Regards,
Community Support Team _ Jing

[ChrisJC]

Hi @v-jingzhang @amitchandak 

Thanks, the link explains why I couldn't upload a pbix!

Here is an uploaded test version: https://we.tl/t-CY6G8vM6lG

Also, here is the data with the result I'm trying to get: https://we.tl/t-QXj7WWrY7u

Column I is the result I'm after. You can see how the 'sums' have been formed in the Excel file.

[ChrisJC]

Thanks @amitchandak I have tried this but it appears to only be calculating the revenue in the month selected, not cumulative of all months in the range selected.

I have an example PBIX but can't seem to attach it