Measure Calculations are Really Slow

[ddbaker]

Hey everyone, I have several measures that I'm using that are really slowing my report down. Wondering if any of you can provide any tips to make them more efficient.

ARR (Monthly) = 
VAR LastInvoice =
    CALCULATE(
        MAX(Invoice[Invoice Date]),
        DATESBETWEEN(
            'Calendar'[Date],
            CALCULATE(
                MIN('Calendar'[Date]),
                ALL('Calendar')
            ),
            LASTDATE('Calendar'[Date])
        )
    )
VAR NextInvoice = 
    CALCULATE(
        MIN(Invoice[Invoice Date]),
        DATESBETWEEN(
            'Calendar'[Date],
            LASTDATE('Calendar'[Date]),
            CALCULATE(
                MAX('Calendar'[Date]),
                ALL('Calendar')
            )
        )
    )
RETURN
IF(
    CALCULATE(
        SUM(Invoice[ARR Amount]), 
        Invoice[Months]=1
    ) = 0,
    IF(
        CALCULATE(MAX(Invoice[Months]),DATESBETWEEN('Calendar'[Date], LastInvoice, LastInvoice))<>1,
        0,
        IF(LastInvoice = 0 || NextInvoice = 0,
            0,
            CALCULATE(
                SUM(Invoice[ARR Amount]),
                DATESBETWEEN(
                    'Calendar'[Date],
                    LastInvoice,
                    LastInvoice
                )
            )
        )
    ),
    CALCULATE(
        SUM(Invoice[ARR Amount]), 
        Invoice[Months]=1
    )
)

Which is referenced in:

ARR (Monthly Summed) = 
VAR CustomerList = VALUES(Customer[Customer ID])
VAR Result = SUMX(CustomerList, _Measures[ARR (Monthly)])
RETURN
Result

Which is referenced in:

ARR (Invoices) = 
[ARR (Monthly Summed)]
+
CALCULATE(
    SUM(Invoice[ARR Amount]),
    Invoice[Months] = 3,
    DATESBETWEEN(
        'Calendar'[Date],
        EOMONTH(LASTDATE('Calendar'[Date]),-3)+1,
        LASTDATE('Calendar'[Date])
    )
)
+
CALCULATE(
    SUM(Invoice[ARR Amount]),
    Invoice[Months] = 6,
    DATESBETWEEN(
        'Calendar'[Date],
        EOMONTH(LASTDATE('Calendar'[Date]),-6)+1,
        LASTDATE('Calendar'[Date])
    )
)
+
CALCULATE(
    SUM(Invoice[ARR Amount]),
    Invoice[Months] = 12,
    DATESBETWEEN(
        'Calendar'[Date],
        EOMONTH(LASTDATE('Calendar'[Date]),-12)+1,
        LASTDATE('Calendar'[Date])
    )
)
+
CALCULATE(
    SUM(Invoice[ARR Amount]),
    Invoice[Months] = 36,
    DATESBETWEEN(
        'Calendar'[Date],
        EOMONTH(LASTDATE('Calendar'[Date]),-36)+1,
        LASTDATE('Calendar'[Date])
    )
)

ARR (Subscriptions) = 
CALCULATE(
    SUM(Subscriptions[Net ARR]),
    Subscriptions[Subscription Status] = "live"||
    Subscriptions[Subscription Status] = "dunning" ||
    Subscriptions[Subscription Status] = "non_renewing" ||
    Subscriptions[Subscription Status] = "paused" ||
    Subscriptions[Subscription Status] = "unpaid"
)

Which both are referenced in:

ARR = 
VAR MaxDate = CALCULATE(MAX('Calendar'[Date]))
RETURN
IF(
    MaxDate = EOMONTH(TODAY(), 0),
    [ARR (Subscriptions)],
    IF(
        MaxDate > EOMONTH(TODAY(),0),
        0,
        [ARR (Invoices)]
    )
)

@lbendlin Thank you for your response. Unfortunately, I'm not advanced enough to know how to utilize your suggestions. So here is some more context.

Here are the tables being used:

Invoices

Customer ID	Subscription ID	Invoice Date	ARR Amount	Months
1	1	1/1/2021	1000	1
1	1	2/1/2021	1000	1
2	2	3/1/2021	2000	12
3	3	4/1/2021	2000	12

Subscriptions:

Subscription ID	Customer ID	Status	Net ARR
1	1	live	1000
2	2	live	2000
3	3	live	2000

The reason why we use both tables is because we can get historical ARR from the invoices, but we have to use the Subscriptions table to get ARR for the current month. So the ARR measure calculates ARR for each customer each month. If the month in context is before today's month, then it uses ARR calculated from the Invoices table, otherwise it uses ARR from the subscriptions table.

The ARR (Invoices) Measure is calculated by taking the ARR (Monthly Summed) measure (I'll talk about this in depth next) and adding it to the sum of ARR for each interval subscription. This is important because a 12 month subscription only has an invoice created every 12 months. So we have to find the sum of ARR in the last 12 months for each customer with 12 month subscriptions. Same logic is applied to 3 month subscriptions etc.

To add further complexity, sometimes monthly subscriptions aren't billed every month (e.g. we gave a customer a free month for whatever reason). The ARR (Monthly) measure checks if the month's ARR amount is 0. Then it checks if the customer had an invoice prior to the month in context and after the month in context. If that's true, then it takes the most recent ARR amount and holds it equal to the current month. The ARR (Monthly Summed) is a SUMX, so it can be analyzed on the customer level.

The ARR measure is being calculated in a way that it can be analyzed on a customer level, so we can find upgrades/downgrades in each month. The desired outcome would be the ability to make a matrix calculating ARR for each customer like this:

Customer ID	1/31/2021	2/28/2021	3/31/2021	4/30/2021	5/31/2021	6/30/2021
1	1000	1000	1000	1000	1000	1000
2	0	0	2000	2000	2000	2000
3	0	0	0	2000	2000	2000

Thank you for your help with this. Let me know if you have any additional questions or if I can clarify anything.

The report is borderline unusable currently, but the numbers are correct. I presume there must be a better way. Thank you in advance for any help you can give!

@mahoneypat any ideas?

[v-shex-msft]

HI @ddbaker,

I'm not so sure why you add so many calculations in your expression. Dax measure formulas are calculated based on row context level, if you nested lots of calculations in the same formula, they will iterator and calculate on each row.

Row Context and Filter Context in DAX - SQLBI

Optimizing DAX expressions involving multiple measures - SQLBI

DAX Best Practices | MAQ Software Insights

For this scenario, I'd like to suggest you do these sub calculation on a variable table and use the iterator function to summary these variable tables that stored the calculation results.

For example:

Measure =
VAR first_Aggregate =
    SUMMARIZE (
        Table,
        Table[Category],
        "Result", 'running calculated formula based on current date period'
    )
RETURN
    SUMX ( first_Aggregate, Result )

Regards,

Xiaoxin Sheng

[mahoneypat · Accepted solution]

The simplest approach would be to modify your Subscriptions table and append it to your invoices table, so it looks something like this. I just added an Invoice Date to it with Date.From(DateTime.LocalNow()), and renamed the Amount column.

Simpler model leads to much simpler DAX.

Pat

[mahoneypat]

All those nested CALCULATEs are slowing things down.  The nested IFs probably aren't good either.  Can you provide sample data and desired output, and some details about your model (tables and relationships)? Even better, share link to your pbix file (e.g., Google Drive, OneDrive).

Pat

[ddbaker]

@mahoneypat I updated the original post with more details.

[lbendlin]

Dump the DATESBETWEEN filters - the majority of them can be replaced with [date]< [last calendar date]

Look into using LASTNONBLANK

Look into using MAXX or similar aggregator functions

Use DAX Studio to evaluate your query plans.

If you like more help please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome. 

[ddbaker]

@lbendlin I updated the original post with more details.