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