Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Imrans123
Advocate V
Advocate V

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

 

1 REPLY 1
Jack_D
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)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.