Forum Discussion
NewAnalyst16
5 years agoFrequent Visitor
Remove certain values from denominator in Measure once X value gets to a certain level
I’m trying to create a measure that tracks how many of our customers are still with us after X number of days. Currently I use this formula: Survival Curve = 1- (divide( CALCULATE( SUM...
v-yalanwu-msft
Community Support
5 years agoHi, NewAnalyst16 ;
You could modify Survival Curve measure as follows:
Survival Curve =
1
- (
DIVIDE (
CALCULATE (
SUM ( 'Attrition Query'[Churn] ),
FILTER (
ALLSELECTED ( 'Attrition Query'[Current Day] ),
ISONORAFTER (
'Attrition Query'[Current Day], MAX ( 'Attrition Query'[Current Day] ), DESC
)
),
FILTER (
ALLSELECTED ( 'Attrition Query'[Current Day] ),
'Attrition Query'[FLOW_END_DATE] <= TODAY ()
)
),
CALCULATE (
COUNT ( 'Attrition Query'[BSCustomerID] ),
ALLSELECTED ( 'Attrition Query'[Current Day] )
)
)
)
Here I have added an additional filter condition, which may be consistent with the idea of DateDifferences less than the Current Day, you could try it.
If it not right, please provide me with more details about your table or share me with your pbix file after removing sensitive data.
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
NewAnalyst16
5 years agoFrequent Visitor
Ideally I would like to use that filter you added, but more like:
FILTER (
ALLSELECTED ( 'Attrition Query'[Current Day]),
'Attrition Query'[DateDifference] >= 'Attrition Query'[Current Day])
I would like a customer to be counted in the measure until the "Current Day" on the X Axis is greater than the individual customer's difference between start date and the current day. For example, a customer should be part of the data on the x-axis from Current Day 1-50 but would not be part of the data when the x-axis is 51-100