Breakdown of sales by month and user group.

[Anonymous]

Hello, I have the following problem which I have been trying to solve for two days but I cannot find a solution.

I have a table with sales which includes the customer id, the products, payment date and a group I have created for different editions of the same product.

I have a second table with customers that had a trial of the product or had a free subscription to the product in the past.

Finally a third table with all the customer ids from both table that I use to connect the tables.

I want to create two 100% stacked column charts, one showing a breakdown of sales for each month/year and the other for speficic editions of a product. The columns  show what type of customers got the product.

Old customers (that had free subscription and now bought it), previous old customers (that had free subscription, bought it and then bought it again), new customers and previous new customers.

I want the same charts for people without trial, trial users and trial users that subscribed more than once.

If we are looking at the aggregates, the numbers are correct. But when using the charts, because I guess measures are being filtered by month they show previous old customers as old customers if the purchase was not in the same month. If I drill up to the year numbers are correct, but I guess when 2019 hit I will have the same problem.

For example you can see in the picture about old/new customers that in the month breakdown I have only old and new customers but in year I also have previous old customers. In the product breakdown because they bought the same products the numbers are correct.

Same with trials. In November there should have been 1 person tagged as "customers with previous trial" in November. In the product breakdonw it is ok because they bought the same product.

So is it possible to have the correct breakdown for each month? I tried to create a summary table but when I add the date I have the same problem.

I attach the charts and some of the measures I have written. I cannot give too much info as it is for my company.

    
Old Customers = CALCULATE(COUNT('UTrial'[MemberId]),'UTrial'[Title]="U 1")
    
Previous Old Customers = CALCULATE(COUNT('Sales Data'[MemberID]), 'UTrial'[Title]="U 1") - CALCULATE(COUNT('UTrial'[MemberId]),'UTrial'[Title]="U 1")
    
New Unique Customers = COUNT('Sales Data'[MemberID]) - CALCULATE(COUNT('Sales Data'[MemberID]),'UTrial'[Title]="U 1")
        
Trial Customers = CALCULATE(COUNT('U Trial'[MemberId]),'U Trial'[Title]="U 1 - 48-Hour Trial")
    
New Customers = COUNT('Sales Data'[MemberID]) - CALCULATE(COUNT('Sales Data'[MemberID]),'UTrial'[Title]="U 1")
    
Previous Trialists = CALCULATE(COUNT('Sales Data'[MemberID]),FILTER('Sales Data','Sales Data'[Net Sales] > 0)) - 'UTrial'[Never Trial Customers] - 'UTrial'[Trial Customers]

Never Trial Customers = COUNT('Sales Data'[MemberID)- (CALCULATE(COUNT('Sales Data'[MemberID]), 'UTrial'[Title] = "U 1 - 48-Hour Trial'))

Previous New Customers = COUNT ( 'Sales Data'[MemberID] ) - [New Unique Customers] - [Old Customers] - [Previous Old Customers]

Product breakdownProduct breakdown

[v-danhe-msft]

Hi @Anonymous,

Could you please share some simple sample data to have a test and post your desired result if possible?

Regards,

Daniel He

[Anonymous · Accepted solution]

@v-danhe-msft Hello, thanks for your interest.

I managed to solve it last night. What I did is create a new column with a counter increasing each time a user purchases a product. Then another column which flags customers based on their number of purchases and if they are old or new customers.

The code I used to create the first column for anyone interested.

U Purchases = CALCULATE(COUNTROWS('Sales Data'),'Sales Data'[Net Sales] > 0,'Sales Data'[Payment Date] >= DATEVALUE ( "30 August 2018" ),FILTER(ALLEXCEPT('Sales Data','Sales Data'[Title (groups)]),'Sales Data'[Title (groups)] = "U 1"),FILTER(ALL('Sales Data'),'Sales Data'[MemberID]=EARLIER('Sales Data'[MemberID])),FILTER(ALL('Sales Data'),'Sales Data'[Payment Date]<=EARLIER('Sales Data'[Payment Date])))