## Desktop

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: 1,386
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.