Forum Discussion
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 ID | Value | Subscription ID | Won Time | Lost time |
| 10016 | $799.00 | 8724 | 12/28/2018 | 2/18/2019 |
| 10021 | $599.00 | 8729 | 11/29/2017 |
3 Replies
- amitchandak
Super User
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
- AnonymousNot 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
Most Valuable Professional
Hi Anonymous ,
was your problem solved?