Forum Discussion

ddbaker's avatar
ddbaker
Helper I
4 years ago
Solved

Help with SUMX using Multiple Criteria

Hi everyone,

 

I have an interesting dataset that gives me the annual amount of the subscription using invoices. The problem is that the annual customers are only billed once a year, but I have to show the annual subscription amount for the 12 months after they are billed. I've written the following measure:

 

 

VAR MaxDate = CALCULATE(MAX(Calendar[Date]))
CALCULATE(
            SUM(Invoice[ARR Amount]), 
            Invoice[Months]=1
        )
        +
        --Calculate ARR from Quarterly Subs
        CALCULATE(
            SUM(Invoice[ARR Amount]),
            FILTER(
                ALL(Invoice),
                Invoice[Months] = 3 && Invoice[Invoice Date] <= MaxDate && Invoice[Invoice Date]>EOMONTH(MaxDate,-3)
            )
        )
        +
        --Calculate ARR from Semi-Annual Subs
        CALCULATE(
            SUM(Invoice[ARR Amount]),
            FILTER(
                ALL(Invoice),
                Invoice[Months] = 6 && Invoice[Invoice Date] <= MaxDate && Invoice[Invoice Date]>EOMONTH(MaxDate,-6)
            )
        )
        +
        --Calculate ARR from Yearly Subs
        CALCULATE(
            SUM(Invoice[ARR Amount]),
            FILTER(
                ALL(Invoice),
                Invoice[Months] = 12 && Invoice[Invoice Date] <= MaxDate && Invoice[Invoice Date]>EOMONTH(MaxDate,-12)
            )
        )
        +
        --Calculate ARR from 3-Year Subs
        CALCULATE(
            SUM(Invoice[ARR Amount]),
            FILTER(
                ALL(Invoice),
                Invoice[Months] = 36 && Invoice[Invoice Date] <= MaxDate && Invoice[Invoice Date]>EOMONTH(MaxDate,-36)
            )
        )

 

 

 

This measure works and gives me the correct number overall, but it isn't able to be used in a table to show the subscription value for each customer - which is really important to see when a customer upgrades/downgrades. My best guess is that's because I'm using FILTER(ALL()). I have tried using SUMX, but haven't been able to figure it out. For reference, the invoice table has a column called Customer ID which maps to a Customer dimension table. Any help would be much appreciated!

  • Hi ddbaker ,

    According to your description, I create a sample, measure is the same with you, it doesn’t calculate based on Customer ID, I use SUMX function in measure2, is it the outcome you expected?

     

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • I don't think that's the reason.  You can represent 

            CALCULATE(
                SUM(Invoice[ARR Amount]),
                FILTER(
                    ALL(Invoice),
                    Invoice[Months] = 3 && Invoice[Invoice Date] <= MaxDate && Invoice[Invoice Date]>EOMONTH(MaxDate,-3)
                )
            )

    as 

            CALCULATE(
                SUM(Invoice[ARR Amount]),
                Invoice[Months] = 3 
                ,Invoice[Invoice Date] <= MaxDate
                ,Invoice[Invoice Date]>EOMONTH(MaxDate,-3)
                
            )

    Maybe something in your data model?

  • Hi ddbaker ,

    According to your description, I create a sample, measure is the same with you, it doesn’t calculate based on Customer ID, I use SUMX function in measure2, is it the outcome you expected?

     

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.