Monthly recurring sales data and gaps

[RamblingFire]

Hi, 

I've got recurring monthly sales data:

Customer a, 2024-01,500

Customer a, 2024-02,500

Customer b, 2024-01,300

Customer b, 2024-02,300

Customer b, 2024-02,300

Customer b, 2024-04,300

Customer c, 2024-04,200

So we "lost" customer a after 2024-02. I'm looking for a way to add this "lost" flag into the table. 

Any ideas or suggestions? 

Aim is to have table of:

Customer a, Lost (billed at some point in period) 

Customer b, Active (continuous billing) 

Customer c, Won (billing currently but not for whole period) 

[weiqb]

create two columns.

1、next_month
we can use the RANKX function to get the next month.

next_month =

VAR _current_customer = sales[customer]

VAR _current_month = sales[num_month]

VAR _rank =

    RANKX (

        FILTER (

            sales,

            sales[customer] = _current_customer

        ),

        sales[num_month],

        ,

        DESC,

        DENSE

    )

VAR _year =

    QUOTIENT (

        _current_month,

        100

    )

VAR _month =

    MOD ( _current_month, 100 ) + _rank

VAR _max_month =

    DATE ( _year, _month, 1 )

RETURN

    YEAR ( _max_month ) * 100

        + MONTH ( _max_month )

2、flag
By checking whether the next_month of the current customer = 202405, we can determine if the months are continuous.

flag =

VAR _next_month = 202405

VAR _current_customer = sales[customer]

VAR _current_sales =

    FILTER (

        sales,

        sales[customer] = _current_customer

    )

VAR _count =

    COUNTROWS ( _current_sales )

VAR _count_non_continuous =

    COUNTROWS (

        FILTER (

            _current_sales,

            sales[next_month] < _next_month

        )

    )

RETURN

    IF (

        _count_non_continuous = 0,

        "Active",

        IF (

            _count - _count_non_continuous > 0,

            "Won",

            "Lost"

        )

    )

[sanalytics · Accepted solution]

@RamblingFire 

You can use below code as well for creating the calculated column 

Flag = 
VAR _MaxDate = MAX( 'Table'[Date] )
VAR _MaxDatebyCustomer = 
CALCULATE(
    MAX( 'Table'[Date] ), ALLEXCEPT( 'Table','Table'[Customer] )
)
VAR _NoOfSales = 
CALCULATE(
    COUNTROWS( 'Table'),
    ALLEXCEPT( 'Table','Table'[Customer] )
)
VAR _Result = 
IF(
    _NoOfSales = 1,"Won",
IF(
    _MaxDate = _MaxDatebyCustomer,
"Active",
"Lost"
) )
RETURN

Below screenshot

Regards

sanalytics

[bhanu_gautam]

@RamblingFire 

create a new column to determine the status of each customer. You can use the following DAX formula to create this column:

DAX
CustomerStatus =
VAR LastMonth = CALCULATE(MAX(Sales[Month]), ALLEXCEPT(Sales, Sales[Customer]))
VAR FirstMonth = CALCULATE(MIN(Sales[Month]), ALLEXCEPT(Sales, Sales[Customer]))
VAR CurrentMonth = MAX(Sales[Month])
RETURN
IF(
LastMonth = CurrentMonth,
IF(
FirstMonth = CurrentMonth,
"Won",
"Active"
),
"Lost"
)