Monthly Attrition Chart

[BrianJ]

@KieftyKids ,

If I understand the requirement correctly, I think the solution below should work for you:

Here’s how I did it:

1. created two disconnected dates tables, one for the start date slicer and one for the end date slicer

2. created the following [In Selected Range] measure to filter out any dates in the bar chart not within the range defined by the slicers:

   In Selected Range =

    IF(
        AND(
            MAX( Dates[MonthnYear] ) >= [Harvest Start Month],
            MAX( Dates[MonthnYear] ) <= [Harvest End Month] 
        ),
        1, 0
    )
   

3. Placed that measuring the filter pane, keeping only those months where [In Selected Range] = 1
4. Created the following measure that does most of the heavy lifting. When you break it up into discrete chunks using variables, it’s pretty straightforward - the denominator is the total number of distinct customer IDs who made a purchase in the selected start month. The numerator is the intersection between the total distinct customer IDs made a purchase in the current row month intersected with the list of customer IDs that form the denominator. The final percentage is just a DIVIDE function of numerator and denominator variables, to protect against a divide by zero situation.

Monthly Stickiness % =

VAR CurrentRowMonth = SELECTEDVALUE( Dates[MonthnYear] )

VAR UniqCustTableStart  =
CALCULATETABLE(
    DISTINCT( Sales[Sale Customer ID]  ),
    FILTER(
        ALLSELECTED(Dates),
        Dates[MonthnYear] = [Harvest Start Month]
    )
)

VAR UniqCustTableCurrent =
CALCULATETABLE(
    DISTINCT( Sales[Sale Customer ID]  ),
    FILTER(
        ALLSELECTED(Dates),
        Dates[MonthnYear] = CurrentRowMonth
    )
)

VAR RetainedCustsFromFirstMoTable = 
INTERSECT( UniqCustTableStart, UniqCustTableCurrent )

VAR Numerator =
COUNTROWS( RetainedCustsFromFirstMoTable )

VAR Denominator =
COUNTROWS( UniqCustTableStart )

VAR Result =
DIVIDE(
    Numerator,
    Denominator,
    BLANK()
)

RETURN Result

I hope this hits the mark. Full solution file attached below.

– Brian
e DNA Forum - Monthly Attrition Chart Solution.pbix (2.6 MB)