cancel
Showing results for
Did you mean:
Helper I

Group Months Count By Month

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
1 ACCEPTED SOLUTION
Community Support

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.

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.

5 REPLIES 5
Community Support

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.

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.

Super User IV

@Mohannad17 , 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.

Proud to be a Super User!

Helper I

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.

Super User IV

@Mohannad17 , 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/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

Proud to be a Super User!

Helper I

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.

Announcements