Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    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.

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

       

      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.

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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.