Forum Discussion

rcopenhagen's avatar
rcopenhagen
Frequent Visitor
7 years ago
Solved

Creating a Calendar for Each Account ID with Revenue Calculation per Account at Point in Time

Hi folks,

 

I am hoping to get some advice on creating the following structure.

 

Today I have a table which has all of my accounts. I have another table which has all of the opportunities associated with those accounts. I use these two tables to calculate annual recurring revenue per account.

 

I would like to have a table which, for each account, has a full calendar of dates and the annual recurring revenue (ARR) on that date. I would then use this to understand acquisition/upgrade/downgrade/churn dates across all accounts by comparing ARR on the start/end dates of a selection. This could also be shown as as a customer acquisition over time chart.

 

It seems like I should be able to generate such a table with DAX, though I'm a little stumped to be honest.

 

For example, the table might end up looking something like this...

 

DateAccount IdARR
 6/1/181$15
6/2/181$20
6/3/181$20
6/1/182$0
6/2/182$0
6/3/182$15
6/1/183$10
6/2/183$10
6/3/183$10

 

I'm imagining that I could do something reasonably similar to what I have for my current ARR calculation on my account table to get the ARR on this new table per date...

 

ARR =
CALCULATE (
    SUM ( Opportunity[Amount (ARR (USD))] ),
    Opportunity[Type] = "New Business"
        || Opportunity[Type] = "New Business Crossell"
        || Opportunity[Type] = "Add-On"
        || Opportunity[Type] = "Upgrade"
        || Opportunity[Type] = "Existing Business"
        || Opportunity[Type] = "Downgrade"
        || Opportunity[Type] = "Churn",
    Opportunity[Mapped Stage] = "Won"
        || Opportunity[Mapped Stage] = "Won (Downgrade)",
    Opportunity[CloseDate] <= 'New Table'[Date]
)

Thanks,

 

Rich

Need Help

2 Replies

    • rcopenhagen's avatar
      rcopenhagen
      Frequent Visitor
      7 years ago

      Worked like a charm. Ended up with the following formulas for the table and to summarize ARR for each line.

       

      Customer Monitoring =
GENERATE (
    'Calendar',
    SUMMARIZE (
        FILTER (
            'Account For Opps',
            'Account For Opps'[HasOpp] = TRUE
        ),
        'Account For Opps'[Id]
    )
)
      ARR = 
CALCULATE (
    SUM ( Opportunity[Amount (ARR (USD))] ),
    FILTER (
        Opportunity,
        Opportunity[Type] = "New Business"
            || Opportunity[Type] = "New Business Crossell"
            || Opportunity[Type] = "Add-On"
            || Opportunity[Type] = "Upgrade"
            || Opportunity[Type] = "Existing Business"
            || Opportunity[Type] = "Downgrade"
            || Opportunity[Type] = "Churn"
    ),
    FILTER (
        Opportunity,
        Opportunity[Mapped Stage] = "Won"
            || Opportunity[Mapped Stage] = "Won (Downgrade)"
    ),
    FILTER (
        Opportunity,
        Opportunity[CloseDate] <= 'Customer Monitoring'[FullDateAlternateKey]
    ),
    FILTER (
        Opportunity,
        Opportunity[AccountId] = EARLIER('Customer Monitoring'[Id])
    )
)