cancel
Showing results for
Did you mean:

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

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:

 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!

1 ACCEPTED SOLUTION
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]
)
``````
4 REPLIES 4
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]
)
``````
Frequent Visitor

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

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 🙂

Frequent Visitor

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors