How to get count of customers without revenue?

[cel]

I am trying to get the number of customers that has no revenue at all. I would like this to be applied to my entire list of customers, but it does not seem to be working.

Taking the following data as an example: I have 10 customers with ID in running order (1000, 1001, 1002, ......., 1010)

What I would like to calculate is how many customers, out of 10, has 0 for total revenue - which in this example would be 5 customers (1001, 1004, 1007, 1009 and 1010).

[cel]

I found a solution that I think might work, but need further assistance.

// Calculates the total revenue for all customers for the past 3 months//

No Rev Last 3 Months =
VAR LatestVisibleDate = LASTDATE (Table[Date])

VAR EarliestRevenueDate = CALCULATE(
FIRSTDATE (Table[Date]),
REMOVEFILTERS (Table)
)

VAR Date3MonthsEarlier = DATEADD(LatestVisibleDate, -3, MONTH)

VAR Result = CALCULATE (Sales[Revenue],
DATESINPERIOD(Table[Date], LatestVisibleDate, -3, MONTH)
)

RETURN IF (Date3MonthsEarlier >= EarliestRevenueDate , Result)

//Calculates total number of customers with 0 revenue from the very first date//

VAR CustWithoutRev = CALCULATE (DISTINCTCOUNT(Sales[ID]),
FILTER (VALUES (Sales[ID]),
CALCULATE(SUM(Sales[Revenue])) = 0)
)

Both codes work on each own, but i'm having trouble fusing them together to get the total number of customers with 0 revenue for the past 3 months. May I get some help with this?

[Arul]

@cel ,

I hope this is your fact table. Can you share the dimension table data as like the above?

Thanks,

[cel]

Hello Arul,

The table looks something like this:

As you can see, those with 0 come from before year 2022. However, I have to include them in my count of customers without revenue.

Also, is there a way where I can filter by dates such as 0 revenue for the past 3 months, past 6 months, past 9 months and past 12 months?

[cel]

Hello @Arul ,

I managed to get this formula and it seems to be working:

Stop Trader = CALCULATE(DISTINCTCOUNT(Consolidated[CustID]),

FILTER (VALUES(Consolidated[CustID]),

Consolidated[Rev_Current FY] = 0))

However, I can't seem to be able to incorporate the date filter. Could you help me with it? I'm thinking I would have to create multiple measures for the different period split, but not sure where and what formula I could use to make it work. (Split by last month, last 3 months, last 6 months, ...)

[v-cgao-msft]

Hi @cel ,

Please try:

0 revenue for the past 3 months = 
VAR _TODAY =
    TODAY ()
VAR _DATE =
    EDATE ( _TODAY, -3 )
VAR _TABLE =
    SUMMARIZE ( 'Table', 'Table'[ID], "SUM", SUM ( 'Table'[Revenue] ) + 0 )
VAR _COUNT =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID] ),
        FILTER ( _TABLE, [SUM] = 0 ),
        'Table'[Date] <= _TODAY
            && 'Table'[Date] >= _DATE
    ) + 0
RETURN
    _COUNT

0 revenue for the past 6 months = 
VAR _TODAY =
    TODAY ()
VAR _DATE =
    EDATE ( _TODAY, -6 )
VAR _TABLE =
    SUMMARIZE ( 'Table', 'Table'[ID], "SUM", SUM ( 'Table'[Revenue] ) + 0 )
VAR _COUNT =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID] ),
        FILTER ( _TABLE, [SUM] = 0 ),
        'Table'[Date] <= _TODAY
            && 'Table'[Date] >= _DATE
    ) + 0
RETURN
    _COUNT

0 revenue for the past 9 months = 
VAR _TODAY =
    TODAY ()
VAR _DATE =
    EDATE ( _TODAY, -9 )
VAR _TABLE =
    SUMMARIZE ( 'Table', 'Table'[ID], "SUM", SUM ( 'Table'[Revenue] ) + 0 )
VAR _COUNT =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID] ),
        FILTER ( _TABLE, [SUM] = 0 ),
        'Table'[Date] <= _TODAY
            && 'Table'[Date] >= _DATE
    ) + 0
RETURN
    _COUNT

0 revenue for the past 12 months = 
VAR _TODAY =
    TODAY ()
VAR _DATE =
    EDATE ( _TODAY, -12 )
VAR _TABLE =
    SUMMARIZE ( 'Table', 'Table'[ID], "SUM", SUM ( 'Table'[Revenue] ) + 0 )
VAR _COUNT =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID] ),
        FILTER ( _TABLE, [SUM] = 0 ),
        'Table'[Date] <= _TODAY
            && 'Table'[Date] >= _DATE
    ) + 0
RETURN
    _COUNT

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[cel]

Hello Gao,

The codes does not seem to be working. The result always shows 0. I have tried to edit the code accordingly, but nothing seems to change the results reflected. Thank you for your help.