Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ChrisJC
Helper I
Helper I

Cumulative Revenue using Billing Start and End Dates

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

ChrisJC_0-1629102947630.png

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 MonthCumulative Revenue
Apr2525
May4065
Jun100165
Jul100265
Aug65330
1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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

081902.jpg

 

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

View solution in original post

6 REPLIES 6
v-jingzhang
Community Support
Community Support

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

081902.jpg

 

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

amitchandak
Super User
Super User

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

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

:

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

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.

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.