Revenue Trend calculation

[pariszhuang]

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

[v-caliao-msft]

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

[pariszhuang]

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.