Need help with calculating new customers measure

Imrans123 — Wed, 06 Dec 2023 20:04:22 GMT

Hi All,

Was wondering if anyone could help. Consider the following data model

Billings Table -

Invoice ID | Customer ID | Amount | Clerk ID | Date

1 | 1 | 50 | 1 | 1st Jan

2 | 1 | 60 | 3 | 1st Feb

3 | 2 | 75 | 2 | 2nd Jan

4 | 3 | 50 | 3 | 1st Mar

5 | 3 | 45 | 1 | 1st Jan

6 | 4 | 75 | 3 | 2nd Feb

Customer Table

Customer ID | Name

1 | john

2 | Jack

3 | Sally

4 | Jeremy

Clerk table

Clerk ID | Clerk Type

1 | 1

2 | 1

3 | 2

I want to write two measures

1. Calculate new customer based on when they first had a non zero invoice

2. Off the new customer list above, how many of them were billed by a a manager (Clerk Type = 2)

So from above table:

Measure 1

New Customers in Jan = 3 (John, Jack and Sally)

New Customers in Feb = 1 (Jeremy)

Measure 2

New Customers in Jan = 0

New Customers in Feb = 1 (Jeremy)

The other people have already had their first billing with a Clerk whose Clerk ID was not equal to 2. As such, they won't count

I have made measure 1 as follows,

NewCust = COUNTROWS ( FILTER ( CALCULATETABLE ( ADDCOLUMNS ( VALUES ( 'Customer'[Customer ID] ), "FirstBilled", CALCULATE ( MIN ( 'Billings'[Date] ), 'Billings'[Amount]>0 ) ), ALL ( DimDates ) ), CONTAINS ( VALUES ( DimDates[Date] ), DimDates[Date], [FirstBilled] ) ) )

I am trying to build measure 2 by adding && clause within ADDCOLUMNS to scan clerk type with no luck.

Any idea what a workaround for this could be??

Re: Need help with calculating new customers measure

Jack_D — Thu, 07 Dec 2023 00:31:50 GMT

New customer =

VAR currentCustomers = VALUES(Billings[Customer ID])

VAR currentDate = MIN(Billings[Date])

VAR pastCustomers = CALCULATETABLE(VALUES(Billings[Customer ID]),

ALL(Billings[Date].[Month],Billings[Date].[MonthNo],Billings[Date].[Year])

, Billings[Date]<currentDate)

VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)

RETURN COUNTROWS(newCustomers)

Number of Customer = calculate( count(Billings[Invoice ID]), Clerk[Clerk ID] = 2)

topic Need help with calculating new customers measure in DAX Commands and Tips

Need help with calculating new customers measure

Re: Need help with calculating new customers measure