Customer Retention

Up

Hi slyfox,

Please try below measures:

No sales in selected Month =
IF (
    CALCULATE (
        SUM ( F_Invoice[Qty] ),
        FILTER (
            ALL ( F_Invoice ),
            F_Invoice[LINK_Customer] = SELECTEDVALUE ( D_Customer[LINK_Customer] )
                && YEAR ( F_Invoice[LINK_Date] ) = SELECTEDVALUE ( D_Date[DATE_YearNumber] )
                && MONTH ( F_Invoice[LINK_Date] ) = SELECTEDVALUE ( D_Date[DATE_MonthNumber] )
        )
    )
        = BLANK (),
    "No sales",
    "Have Sales"
)

No sales in previous Month =
VAR previousYearNumber =
    IF (
        SELECTEDVALUE ( D_Date[DATE_MonthNumber] ) = 1,
        SELECTEDVALUE ( D_Date[DATE_YearNumber] ) - 1,
        SELECTEDVALUE ( D_Date[DATE_YearNumber] )
    )
VAR previousMonthNumber =
    IF (
        SELECTEDVALUE ( D_Date[DATE_MonthNumber] ) = 1,
        12,
        SELECTEDVALUE ( D_Date[DATE_MonthNumber] ) - 1
    )
RETURN
    IF (
        CALCULATE (
            SUM ( F_Invoice[Qty] ),
            FILTER (
                ALL ( F_Invoice ),
                F_Invoice[LINK_Customer] = SELECTEDVALUE ( D_Customer[LINK_Customer] )
                    && YEAR ( F_Invoice[LINK_Date] ) = previousYearNumber
                    && MONTH ( F_Invoice[LINK_Date] ) = previousMonthNumber
            )
        )
            = BLANK (),
        "No sales",
        "Have Sales"
    )

No sales in previous 3 Month =
IF (
    CALCULATE (
        SUM ( F_Invoice[Qty] ),
        DATESINPERIOD (
            D_Date[DATE_Date].[Date],
            DATE ( SELECTEDVALUE ( D_Date[DATE_YearNumber] ), SELECTEDVALUE ( D_Date[DATE_MonthNumber] ), 1 ),
            -3,
            MONTH
        ),
        FILTER (
            ALL ( F_Invoice ),
            F_Invoice[LINK_Customer] = SELECTEDVALUE ( D_Customer[LINK_Customer] )
        )
    )
        = BLANK (),
    "No sales",
    "Have Sales"
)

Best regards,

Yuliana Gu