Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
RamblingFire
Regular Visitor

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) 

1 ACCEPTED SOLUTION
sanalytics
Super User
Super User

@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

sanalytics_0-1740476122057.png

 

Regards

sanalytics

View solution in original post

3 REPLIES 3
weiqb
Frequent Visitor

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

weiqb_0-1740650844892.png

 

sanalytics
Super User
Super User

@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

sanalytics_0-1740476122057.png

 

Regards

sanalytics

bhanu_gautam
Super User
Super User

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.