Forum Discussion

DataStraine's avatar
DataStraine
Icon for Advocate I rankAdvocate I
5 years ago
Solved

Cumulative Sum When Not Desired?

Hello all. I'm very new to Power BI and I'm stumped. I am trying to wirte a calculation to determine where a customer is considered churned on their 7th month of inactivity. I have calculations for Inactive Customer and Churned Customer;  however, when I go to try to get the calculations to return the resutls on pure monthly basis, it returns a culmative count of these customers across the months, i.e. if a customer churned 1/1/21 then it would still be counted in 2/1/21, 3/1/21 and so forth. What I need instead is for it to only return a count customers that churned in a particular month.

I have figured out a way to ge the correct result by creating a table of chunred customers in memory, but I've been told that I need to do it as a calculation(s). Please find below my DAX forumals for each step in the process. Thank you in advanced for all your help! 


Calculations for Inactive Customer

 

Inactive Customer Count = 
VAR __CURRENT_VALUES =
	FILTER(
		KEEPFILTERS(VALUES('Customers'[ID])),
		ISBLANK([Total Billable Count]) -- Number of Billable Instances
	)
VAR __EXISTING_VALUES =
	FILTER(
		KEEPFILTERS(__CURRENT_VALUES),
			ISBLANK(
				CALCULATE(
					[Total Billable Count],
					FILTER(ALL('Calendar'[Date]), 'Calendar'[Date] < MIN('Calendar'[Date])) -- Calendar is a table with all applicable calendar dates
				)
			)
	)
RETURN
	COUNTROWS(
		EXCEPT(__CURRENT_VALUES, __EXISTING_VALUES)
	)

 

 

Calculations for Churned Customer

 

 

LostCustomerWhen = CALCULATE(
                        [Inactive Customer Count],
                        DATESINPERIOD('Calendar'[Date],
                        LASTDATE('Calendar'[Date]),
                        -7, MONTH
                    )
)

 


Calculated Table that Returns the Correct Results

Test Churn Table = 
VAR _MonthYear = SUMMARIZE('Calendar', 'Calendar'[Month-Year-Date])
VAR _Customers = SUMMARIZE(Customers, Customers[Id], Customers[Customer Name])
VAR _CustomerCross = CROSSJOIN(_MonthYear, _Customers)
VAR _CustomerCrossLost = ADDCOLUMNS(_CustomerCross, "Churned", [LostCustomerWhen])
VAR _OnlyChurned = FILTER(_CustomerCrossLost, [Churned] =1 )

RETURN 
FILTER(GROUPBY(  
    _OnlyChurned,
    Customers[Id], Customers[Customer Name], [Churned], 
    "Churned Month", MINX(CURRENTGROUP(), [Month-Year-Date])  
    ), [Churned Month] <= TODAY()
)  

 

  • Hi,

    There should definitely be Month name in the visual for the measure to get a row context.  Write this additional measure and drag it to your matrix visual

    Measure 2 = SUMX(GENERATE(VALUES('Calendar'[Month name]),VALUES('Calendar'[Year])),[Measure])

    You may drag the Customer Name off the matrix, if you so wish

12 Replies