Cumulative total gives me same result as monthly total even though changed the formula

DAX DAX Calculations
1

I have two formulas that give me the same result. The result I am getting is the monthly total.
This formula correctly gives me only the ABC subscriptions added in the current month:

ABCs Started =
CALCULATE (
[ABC Order Count All],
USERELATIONSHIP(‘Recurring Subscriptions’[Subscription Start Date],DateTable[Date]),
FILTER (
values(‘Recurring Subscriptions’[Subscription Start Date]),‘Recurring Subscriptions’[Subscription Start Date]<=max(DateTable[Date])),
FILTER (
values(‘Recurring Subscriptions’[Subscription Start Date]),‘Recurring Subscriptions’[Subscription Start Date]>=MIN(DateTable[Date]))
)

This formula is trying to get the cumulative total but it gives me the monthly total only.
ABCs Cumulative Started To Date =
CALCULATE (
[ABC Order Count All],
USERELATIONSHIP(‘Recurring Subscriptions’[Subscription Start Date],DateTable[Date]),
FILTER (
all(‘Recurring Subscriptions’[Subscription Start Date]),‘Recurring Subscriptions’[Subscription Start Date]<=max(DateTable[Date])

))

Note, I need to use USERELATIONSHIP because I have another calculation that needs to use the END DATE. Both of these work fine to count the users that started and ended in the month. The problem is when I try to get the cumulative, it only gives me the total for the month.

2

Hello @ScottTPA,

For Cumulative Totals instead of selecting “ALL” replace it with “ALLSELECTED” and also replace the ‘Recurring Subscriptions’[Subscription Start Date]) with the Dates Table. So your code should be like this overall -

ABCs Cumulative Started To Date =
CALCULATE ( [ABC Order Count All] , 
      USERELATIONSHIP( ‘Recurring Subscriptions’[Subscription Start Date] , DateTable[Date]) ,
            FILTER ( ALLSELECTED ( DateTable ) ,
                    DateTable[Date] <= MAX( DateTable[Date] ) ) )

Also for calculation of Current Month Total you could have also used DATESMTD which could be -

ABCs Started =
CALCULATE ( [ABC Order Count All] , 
      USERELATIONSHIP( ‘Recurring Subscriptions’[Subscription Start Date] , DateTable[Date]) ,
            DATESMTD( DateTable[Date] ) )

I’ve also attached the screenshot and file of my working indicating both the steps i.e. wrong one and the correct one for Cumulative Totals.

Hoping you find this useful and can help you to achieve your desired analysis.

Please feel free to write back in case I’ve missed out on anything.

Thanks & Regards,
Harsh

Cumulative Totals using USERELATIONSHIP
Cumulative Totals using USERELATIONSHIP972×809 126 KB

Cumulative Totals using USERELATIONSHIP Function.pbix (439.5 KB)

3

Hi @ScottTPA, please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

https://analysthub.enterprisedna.co/dax-clean-up

4

@Harsh Thank you. That helped me fix a few things in my calculations. The DATESMTD cleaned up some related calculations that weren’t working correctly also.

@EnterpriseDNA, thank you. Great tool.