Forum Discussion
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- lbendlinSuper User
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?
- v-yanjiang-msftCommunity Support
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.