Group Months Count By Month

[Anonymous]

Hi Everyone,

I Currently have a table that displays the customers and the month that the we gained that customer on or lost the customer, I'm trying to create a new calculated table to show how many customers we gained or lost by month.

The table of customers that I currently have.

Customer Number	Month Of Customer Gain	Month Of Customer Lose
123	October	-
124	October	-
125	September	-
126	-	August
129	-	October

The calculated table that I'm trying to create

Month	Gained Customers	Lost Customers
October	2	1
September	1	0
August	0	1

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

It’s my pleasure to answer for you.

According to your description,I think you may need to create a new table with a single column as silcer,then create a measure to filter data in filter pane.

Like this:

Measure = 
var tab =
CALCULATETABLE(
            DISTINCT('Customer Status'[Customer Number]),
            FILTER(
                ALL('Customer Status'),
                [Month Of Join/rejoin] in DISTINCT('Calendar'[Month Name])||
                [Month Of Lose] in DISTINCT('Calendar'[Month Name])
            )
)
var tab1 = 
CALCULATETABLE(
            DISTINCT('Customer Status'[Customer Number]),
            FILTER(
                ALL('Customer Status'),
                [Month Of Join/rejoin] in DISTINCT('Calendar'[Month Name])
            )
)
var tab2 = 
CALCULATETABLE(
            DISTINCT('Customer Status'[Customer Number]),
            FILTER(
                ALL('Customer Status'),
                [Month Of Lose] in DISTINCT('Calendar'[Month Name])
            )
)
return
IF(
    HASONEVALUE(Slicer[Slicer]),
    IF(
        SELECTEDVALUE(Slicer[Slicer])="join" ,
        IF(
            MAX('Customer Status'[Customer Number]) in tab1,
            1,0
        ),
        IF(
            SELECTEDVALUE(Slicer[Slicer])="lose",
            IF(
                MAX('Customer Status'[Customer Number]) in tab2,
                1,0
            )
        )
    ),
    IF(
            MAX('Customer Status'[Customer Number]) in tab,
            1,0
    )
)

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[amitchandak]

@Anonymous , Can you share better sample data and sample output in a table format? Or a sample pbix after removing sensitive data. Also what happens if a customer does not come for one month and comes again a month next to it.

[Anonymous]

This is the sample pbix 

I made a partial solution to this. You will find it in the sample, but my issue with my solution is that I can't filter out the specific customers from the customers' list due to it being a measure. I'm trying to find a way to make a calculated table that lets me filter out, for example, a list of the new customers in October.

[amitchandak]

@Anonymous , it depends on your gain and lost definition, what is that. I have two blogs, refer if those can help

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458

[Anonymous]

if a customer got lost on october it will show october in front of the customer number and under the column "month of lose", basically I would like to transfer the first table to the second table, I've managed to do something with a measure using a virtual relationship with month name from the calendar table but my issue with that measure that I can't filter the customers out if I click on the measure, it only filter the month.