Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Future Projections on Subscriptions

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?

Need Help

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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.