cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mohannad17
Helper I
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 NumberMonth Of Customer GainMonth Of Customer Lose
123October-
124October-
125September-
126-August
129-October

 

 

The calculated table that I'm trying to create

 

MonthGained CustomersLost Customers
October21
September10
August01
1 ACCEPTED SOLUTION
v-janeyg-msft
Community Support
Community Support

Hi, @Mohannad17 

 

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

10.png

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.

View solution in original post

5 REPLIES 5
v-janeyg-msft
Community Support
Community Support

Hi, @Mohannad17 

 

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

10.png

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.

View solution in original post

amitchandak
Super User IV
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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

 

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
Super User IV
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...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.