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

[rcopenhagen]

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...

Date	Account Id	ARR
6/1/18	1	$15
6/2/18	1	$20
6/3/18	1	$20
6/1/18	2	$0
6/2/18	2	$0
6/3/18	2	$15
6/1/18	3	$10
6/2/18	3	$10
6/3/18	3	$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

[Greg_Deckler · Accepted solution]

Take a look at the GENERATE command.

[rcopenhagen]

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])
    )
)