Reply
Highlighted
Frequent Visitor
Posts: 2
Registered: ‎03-06-2017

Revenue Trend calculation

Dear all,

 

I have just started using Power BI, and trying to do dashboards for the company.

 

My company is selling subscription based software and each subscription from each customer has varying length (eg. 3 months, 6 months, 1 year). The rates of each subscription product type varies (eg. 5K, 10k)

 

Currently I want to view the revenue as at a few points in time (eg. 31 Jan 2017, 31 Dec 2016). Thus, I want to be able to know any subscription revenue that is earned as at that date.

 

The following table shows the example:

Customer

Amount

Licence subscription Period

As at 31 Dec 2016

As at 31 Jan 2017

Tim

5,000

1/1/2017 – 31/03/2017

0

5,000

Paul

10,000

1/12/2016 – 31/03/2017

10,000

10,000

Siti

15,000

1/11/2016 – 31/12/2015

15,000

NA

Logan

20,000

1/10/2016 – 15/01/2017

20,000

NA

Total

50,000

 

45,000

15,000

 

Currently I have used the following formula below, and it only shows the total of all revenue from the customers above (of 50,000) and does not varies based on my explicit filtering.

 

RevPeriod = CALCULATE(
	sum('L F DATA'[USD.A]),	
	filter(
		allselected('All Users Raw Data'[Month Review]),
		'All Users Raw Data'[Month Review]<=MAX('L F DATA'[Recurring End Date])
	)
)

 

Moderator
Posts: 765
Registered: ‎03-06-2016

Re: Revenue Trend calculation

Hi @pariszhuang,

 

Do you use a table visual in your report? It's hard for us to understand you detail table and report structure. Please provide us the detail information and the expected result, so that we can make further analysis.

 

Based on my understand, you should have start date and end date column for your licence subscription. So you can check if the time point is inside this period or not.

 

Regards,

Charlie Liao

Frequent Visitor
Posts: 2
Registered: ‎03-06-2017

Re: Revenue Trend calculation

Dear Charlie,

 

As the information that I am using is company's property, it is confidential thus I can't show you the details. However, I have a seperate start date and end date column in my licence subscription. As the start and end date span over a few months. I am trying to calculate the revenue as at each time point over the different months as stated in the following table. An example is stated below:

 

- Eg. Customer A starts and end the licence period over 1 September to 5 December 2016 of $2,000 in total.

EOMonth September the annualised revenue contributed from Customer A is $2,000.

EOMonth October the annualised revenue contributed from Customer A is $2,000.

EOMonth November the annualised revenue contributed from Customer A is $2,000.

EOMonth December the annualised revenue contributed from Customer A is $2,000.

 

 

image.png