I want to exclude zero billers from my analysis only when they have no revenue in particular month

[SR1122]

I have a requirement where the customer needs to be excluded from the analysis if it has zero bills in that particular month.

I am working with one table which contains all the transactions.

EG.

Date	Customer ID	Revenue per transaction	Total Products
23/01/2022	29	0	5
24/01/2022	29	1.4	6
24/01/2022	29	2.4	6
25/01/2022	29	5	10
25/01/2022	28	0	10
23/01/2022	28	0	16
21/01/2022	28	0	17
16/01/2022	28	0	21
23/02/2022	29	0	32
24/02/2022	29	1.4	123
24/02/2022	29	2.4	112
25/02/2022	29	5	312
25/02/2022	28	0	231
23/02/2022	28	100	33
21/02/2022	28	0	112
16/02/2022	28	0	213

As you can see in the example below I want to ignore the count of company 29 in Jan and ignore the product counts.
But i want to count the company 29 next month as it has revenue and want to count the product.

I have tried the following but the problem is in Feb 2022 it will ignore the products which has zero revenue. 

Customer count = CALCULATE(DISTINCTCOUNT(Sales[companyid]),Sales[amount]<>0)

[v-xiaosun-msft]

Hi @SR1122 ,

According to your description, it seems that you want to ignore the products which has zero revenue in Jan, but do not want to ignore the products which has zero revenue in Feb.

Please create a column.

Customer count =
IF (
    MONTH ( 'Table'[Date] ) = 1,
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Customer ID] ),
        'Table'[Revenue per transaction] <> 0
    ),
    IF (
        MONTH ( 'Table'[Date] ) = 2,
        CALCULATE ( DISTINCTCOUNT ( 'Table'[Customer ID] ) )
    )
)

Final output:

If the result is not what you need, please describe your needs more clearly and you'd better show us how your expected output looks like.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SR1122]

Thanks for the answer, this was very useful.

Basically, i want to apply this logic not just for Jan and Feb.

The data set I have contains multiple years and this need to be applied every month to every customer.

The requirement is if the customer has revenue count all the products and if the customer doesn't have any revenue ignore all the products count. 

[v-xiaosun-msft · Accepted solution]

Hi @SR1122 ,

According to your description, I made a sample with more years and months. Please follow these steps.

Create two columns to return the years and months of the "DATE" column.

month of date =
MONTH ( 'Table (2)'[Date] )

year of date =
YEAR ( 'Table (2)'[Date] )

Then create a column to return the expected result.

result =
IF (
    COUNTROWS (
        FILTER (
            'Table (2)',
            'Table (2)'[month of date] = EARLIER ( 'Table (2)'[month of date] )
                && 'Table (2)'[year of date] = EARLIER ( 'Table (2)'[year of date] )
                && 'Table (2)'[Customer ID] = EARLIER ( 'Table (2)'[Customer ID] )
                && 'Table (2)'[Revenue per transaction] <> 0
        )
    ) > 0,
    CALCULATE ( DISTINCTCOUNT ( 'Table (2)'[Customer ID] ) )
)

Final output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.