Calculating historical salesperson

[EiA]

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:

InvoiceID	Client	Department	InvoiceDate
100	1	A	01.01.2023
101	1	A	01.02.2023
102	2	A	01.01.2023

Salespersons per Client/Department over time:

Client	Department	SalesPerson	From	To
1	A	JW	01.06.2022	15.01.2023
1	A	MS	16.01.2023	01.06.2023
2	A	MS	01.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!

[johnt75 · Accepted solution]

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]

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

[Thomas_Daubert]

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 🙂

[EiA]

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