cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Need help with calculating new customers measure

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 (
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??

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