Forum Discussion

RamblingFire's avatar
RamblingFire
Regular Visitor
1 year ago
Solved

Monthly recurring sales data and gaps

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) 

  • sanalytics's avatar
    sanalytics
    1 year ago

    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

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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"
    )

  • sanalytics's avatar
    sanalytics
    Icon for Super User rankSuper User
    1 year ago

    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

  • weiqb's avatar
    weiqb
    Frequent Visitor
    1 year ago

    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"
            )
        )