Remove certain values from denominator in Measure once X value gets to a certain level

[NewAnalyst16]

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!

[v-yalanwu-msft]

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.

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]

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

[NewAnalyst16]

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.