Forum Discussion

ddbaker's avatar
ddbaker
Icon for Helper I rankHelper I
4 years ago
Solved

Measure Calculations are Really Slow

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 IDSubscription IDInvoice DateARR AmountMonths
111/1/202110001
112/1/202110001
223/1/2021200012
334/1/2021200012

 

Subscriptions:

Subscription IDCustomer IDStatusNet ARR
11live1000
22live2000
33live2000

 

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 ID1/31/20212/28/20213/31/20214/30/20215/31/20216/30/2021
1100010001000100010001000
2002000200020002000
3000200020002000

 

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?

  • 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

     

     

6 Replies

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

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee

    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

     

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee

    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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable

    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