Forum Discussion
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
- Ashish_Mathur
Super User
Hi,
Please share some data and show the expected result.
- DataStraine
Advocate I
Ok apparently the internet ate my reply. Please find the data at the following link (google sheets)
https://docs.google.com/spreadsheets/d/1RetQPnuNIN7lQOFnmlrTYMmPqXryJyKWdfyini5eNo4/edit?usp=sharing- Ashish_Mathur
Super User
- DataStraine
Advocate I
Looks like this worked! Could you explain this part of the code for me? I'm having a hard time understanding why it's greather than 0
Is to ensure that 7 months ago there was some billable count?