Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
EiA
Frequent Visitor

Calculating historical salesperson

Hi, I have an issue calculating which salesperson was responsible for a client/department when an historic invoice was issued.


My tables are similar to these:

 

Invoices:

InvoiceIDClientDepartmentInvoiceDate
1001A01.01.2023
1011A01.02.2023
1022A01.01.2023

 

Salespersons per Client/Department over time:

ClientDepartmentSalesPersonFromTo
1AJW01.06.202215.01.2023
1AMS16.01.202301.06.2023
2AMS01.01.2022

31.12.2023

 

Is there a way to get a calculated column in Invoices reflecting which salesperson was responsible for Client/department when invoice was issued (based on invoice date)? Meaning Invoice 100 would be assigned JW and 101 would be MS.

 

Or any other better approach?


Thanks a lot!

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

You could create a calculated column like 

Salesperson =
VAR InvoiceDate = 'Invoices'[Invoice Date]
VAR Client = 'Invoices'[Client]
VAR Department = 'Invoices'[Department]
RETURN
    SELECTCOLUMNS (
        FILTER (
            'Salespersons',
            'Salespersons'[Department] = Department
                && 'Salespersons'[Client] = Client
                && 'Salespersons'[From] <= InvoiceDate
                && (
                    'Salespersons'[To] >= InvoiceDate
                        || ISBLANK ( 'Salespersons'[To] )
                )
        ),
        "@val", 'Salespersons'[SalesPerson]
    )

View solution in original post

4 REPLIES 4
johnt75
Super User
Super User

You could create a calculated column like 

Salesperson =
VAR InvoiceDate = 'Invoices'[Invoice Date]
VAR Client = 'Invoices'[Client]
VAR Department = 'Invoices'[Department]
RETURN
    SELECTCOLUMNS (
        FILTER (
            'Salespersons',
            'Salespersons'[Department] = Department
                && 'Salespersons'[Client] = Client
                && 'Salespersons'[From] <= InvoiceDate
                && (
                    'Salespersons'[To] >= InvoiceDate
                        || ISBLANK ( 'Salespersons'[To] )
                )
        ),
        "@val", 'Salespersons'[SalesPerson]
    )
EiA
Frequent Visitor

Excellent @johnt75  - seems to work exactly as intended! Thank you

Thomas_Daubert
Frequent Visitor

Hello,

 

The best way (IMO) is to join the 2 tables directly in an SQL query.

it would be something like :

A JOIN B on A.invoice_date >= b.from and a.invoice_date < b.to

 

< or <= depends on the original data model 🙂

 

Thanks for the suggestion @Thomas_Daubert , I don't have access to SQL directly, but I am sure this could have worked well too

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.