Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Optimizing Cumulative Measures in DAX

I have a data model with subscription based sales data. This means that a customer has a sale date (start date), and from that date until the lost date (end date) (if there is one) pays a monthly subscription fee. A customer can pay for multiple subscriptions at a time. 
For reporting I work with multiple Cumulative Measures. Some of these can use the 'regular' Cumulative pattern, but because of the subscription not everything can be calculated this way, for example 'Cumulative Won Sales'. Because some sales that are not currently won anymore (because a customer cancelled subscription) should be included in the Cumulative Won Sales.

See below:
Cumulative Sales, includes also deals that were won but now Lost.

 

Cumulative Sales = 
CALCULATE( [Total Sales],
    FILTER( ALLSELECTED('Dates'[Date]),
        'Dates'[Date] <= MAX( 'Dates'[Date] ) ),
        USERELATIONSHIP(Deals[Won Time (Clone For Reports)],Dates[Date] )
)

 

 
Cumulative Lost Sales (MRR is monthly recurring revenue):

 

Cumulative Lost MRR = 
CALCULATE( [Lost MRR],
    FILTER( ALLSELECTED('Dates'[Date]),
        'Dates'[Date] <= MAX( 'Dates'[Date] ) ) )

 


I can then calculate Cumulative Won Sales as follows:

 

Cumulative Won MRR = [Cumulative Sales]-[Cumulative Lost MRR]

 

 
This is already pretty slow, but it gets even slower when I calculate other subscription related metrics, like 'Average revenue per account':

 

ARPA = DIVIDE([Cumulative Won MRR], [Cumulative Won Customers])

 

 The performance of these metrics is very bad because of the building up of cumulative measures. I have not been able to find any other way to do this, can I somehow optimize these measures?

See example data here https://docs.google.com/spreadsheets/d/1SmYKwySkYFA10v6hNjPYlZeOjRgb3jFNBPMEUL9J5XI/edit?usp=sharing

An example of what I want to see: For the two customers below (10016 and 10021): For 10016 I want to see a cumulative won sales monthly off 799$, between the won date and the lost date. For customer 10021 I want to see a cumulative won sales monthly of 599$, from the won date up until today. 

Organization IDValueSubscription IDWon TimeLost time
10016$799.00872412/28/20182/18/2019
10021$599.00872911/29/2017 

3 Replies

  • Anonymous , there was a video from Guyinacube, where they suggest using datekey to join with date table. in both date and transaction table create a date as the whole number and join on that

     

    Date = format([Date],"YYYYMMDD") // make it as whole number

     

    Join this column in both dates

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks, I did see that video. That is not helping me here though, I need to think of another way to build the cumulative won measure.

  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional

    Hi Anonymous ,

     

    was your problem solved?