Number of NEW customers each month

[Anonymous]

Hi PowerBI experts,

I would like to ask you for help. I have a problem with a task. I need to calculate number of NEW customers each month.

I have two tables in my data model - 'tb_item_sales' and 'DateTable' (generated based on min and max Posting date).

'tb_item_sales':

Orde number	Transaction ID	Posting date	BP Code	ItemCode	LineTotal	BP Project
10001	1	5/26/2020	C00483	2025	105.75	A
10001	1	5/26/2020	C00483	2026	105.75	A
10002	1	5/23/2020	C00483	2166	557.5	A
10003	1	3/29/2020	C00483	3246	82.16	A
10004	1	3/25/2020	C00483	3247	165.48	A
10005	1	4/26/2020	C00484	2025	248.8	A
10006	1	4/23/2020	C00484	2166	332.12	A
10007	1	1/29/2020	C00484	3246	415.44	A
10008	1	1/25/2020	C00484	3247	498.76	A
10009	1	5/26/2020	C00480	2025	105.75	B
10010	1	5/23/2020	C00480	2166	557.5	B
10011	1	3/29/2020	C00480	3246	82.16	B
10012	1	3/25/2020	C00480	3247	165.48	B
10013	1	4/26/2020	C00479	2025	248.8	B
10014	1	4/23/2020	C00479	2166	332.12	B
10015	1	1/29/2020	C00479	3246	415.44	B
10016	1	1/25/2020	C00479	3247	498.76	B
10017	1	4/26/2020	C00485	3248	1000	B
10018	1	5/26/2020	C00486	3248	1111	B

Here is what i have:

New Customer = COUNTROWS(FILTER(SUMMARIZE(ALL(tb_item_sales[BP Code]),tb_item_sales[BP Code],"ThisMonthSales",[Net Total Sales],"PreviousSales",[Previous Sales]),[Net Total Sales]>0 &&[PreviousSales]=0 ))

Net Total Sales = CALCULATE(sum(tb_item_sales[LineTotal]),FILTER(tb_item_sales,tb_item_sales[Transaction ID]=1))

MIN date BP code = CALCULATE(MIN(tb_item_sales[Transaction Date]),ALLEXCEPT(tb_item_sales,tb_item_sales[BP Code]))

Previous Sales = CALCULATE (
                SUM( tb_item_sales[LineTotal]),
                FILTER (
                    ALL ( tb_item_sales ),
                    tb_item_sales[Transaction Date]< [MIN date BP code]
                ))

My output table should looks like below (red frame):

New customer means that the previous sales is equal to 0 (before particular each month). That is why C00485 is the new customer in April.

We have a one new customer in project "B"  (in total) and zero new customers in project "A" in April ("BP Project" will be a slicer).

Thanks in advance.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a measure

NewCustomersSet = 
    VAR CurrentCustomers = FILTER(VALUES ( 'Table'[BP Code] ),MIN('Table'[Transaction ID])=1)
    VAR OldCustomers =
        FILTER (
            CurrentCustomers,
            CALCULATE (
                MIN ( 'Table'[Posting date] ),
                ALL ( 'date' )
            ) < MIN ( 'date'[Date] )
        )
    RETURN COUNTROWS ( EXCEPT ( CurrentCustomers, OldCustomers ) )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you @v-juanli-msft  It works perfectly! the solution is really profesionall. Now i can resign with my temporary solution - calculated column like below:

New customer = 
VAR mycount =
    CALCULATE (
        COUNTROWS (tb_item_sales),
        FILTER (
            ALLEXCEPT ( tb_item_sales, tb_item_sales[BP Code] ),
            tb_item_sales[Data księgowania] < EARLIER ( tb_item_sales[Data księgowania])
        )
    )
RETURN
    IF ( mycount = 0, "Yes" )