cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NewAnalyst16
Frequent 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('Attrition Query'[Churn]),

    FILTER(

        ALLSELECTED('Attrition Query'[Current Day]),

        ISONORAFTER('Attrition Query'[Current Day], MAX('Attrition Query'[Current Day]), DESC)

    )

),CALCULATE(

COUNT('Attrition Query'[BSCustomerID]),

ALLSELECTED('Attrition Query'[Current Day]))))

 

This takes the running total of all customers who leave us by the day they do.

[Current Day] is a calculated column which returns either the day of their contract they left us or, if they haven’t left us, the datediff between their start day and today.

Current Day = if('Attrition Query'[StillFlowing]=0, if('Attrition Query'[FLOW_END_DATE]>today(),datediff('Attrition Query'[FLOW_START_DATE], Today(), DAY),DATEDIFF('Attrition Query'[FLOW_START_DATE], 'Attrition Query'[FLOW_END_DATE], DAY)), datediff('Attrition Query'[FLOW_START_DATE], Today(), DAY))

 

However, I run into one main issue stemming from the fact that I have a variety of customers starting at different time periods, so they are all counted in the denominator. The formula currently says that my 1-year survival is 72%, however that is far too high as the real number is 56.4%. The issue is that customers who have not yet been with us a year are being factored into the denominator, even though they could conceivably leave before they get to a year.

 

What I would like to do is exclude any customers from the measure when their DateDifference: DateDifference = DATEDIFF('Attrition Query'[FLOW_START_DATE], today(), DAY)

is less than the Current Day in the measure. So if we were looking at an X axis, the higher we go on the X axis, the smaller the Denominator gets.

 

Any help would be greatly appreciated, thanks!

3 REPLIES 3
v-yalanwu-msft
Community Support
Community Support

Hi, @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.

v-yalanwu-msft_0-1623399576147.png

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.

 

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

Thanks for responding and sorry for the late reply. When I add the additional filter, I get an error that "A single value for column 'FLOW_END_DATE' cannot be determined". It says this is because there is no aggregation in the formula.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Top Solution Authors