Future Projections on Subscriptions

[Anonymous]

I am trying to create a chart that shows future projections based on subscriptions. Each customer will have a subscription fee and an expiration year. For example, Customer A has a subscription fee of $10 and their subscription term ends in 2020. Customer B pays $20 and their subscription ends in 2021. How could future values be added to an existing graph so that 2019 would show 10+20 and then 2020 would show 20? Essentially, a graph already exists showing amount collected per year, but we want to add future to this graph. Is there an easy way to do this?

[Ashish_Mathur]

Hi,

I may need to carry out a transaction so that paste data in the body of the message in a format that I can take it to an Excel file.

[MFelix]

Hi @Anonymous ,

Although you didn't detailed the format of your data I assume the following two tables:

Subscriptions:

Customer	Subscription Value	End of Subscription	Start of Subscription
A	10	2020	2015
B	20	2021	2018

Years:

Year
2015
2016
2017
2018
2019
2020
2021
2022

I now have added the following measure:

Subscription calculation =
CALCULATE (
    SUM ( 'Subscription'[Subscription Value] );
    FILTER (
        ALL ( 'Subscription'[End of Subscription]; Subscription[Start of Subscrition] );
        'Subscription'[Start of Subscrition] <= MAX ( Years[Year] )
            && 'Subscription'[End of Subscription] >= MAX ( Years[Year] )
    )
)

Result is below (X-axis is the column on the Year table):

As you can see until 2018 the values is only subscription A then both and on 2021 only B.

The year column cvan be adjusted to a date column if needed.

Regards,

MFelix