03-20-2017 02:19 PM
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:
Licence subscription Period
As at 31 Dec 2016
As at 31 Jan 2017
1/1/2017 – 31/03/2017
1/12/2016 – 31/03/2017
1/11/2016 – 31/12/2015
1/10/2016 – 15/01/2017
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]) ) )
03-22-2017 01:08 AM
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.
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.