How can I place the new sales supervisor of a seller since the “x” date?

[Darwin98]

I have a table where sales are recorded such as what was sold, seller code, among other things. Also, another table where the seller code, supervisor, name, last name, etc. are located. For the sales table, I create a calculated column using DAX related and it shows me in the same sales table the supervisor of the seller who made the sale.

Now, how would it be if from the “x” date onwards a seller has another supervisor and I want the sales table to show me the new supervisor of the seller, but keep the supervisor he had before the “x” date?

[WinterMist]

@Darwin98 

Are you able to put your PBIX in Google Drive, and then share the link in this thread?

I made a best guess at your model in my test data, but if your model is different in any way, the solution will usually not work.

NOTE: Whenever posting q's like this, the more of your model you can share, the better.

- Is there a [Date] field in the 'Seller' table like I guessed, that tells PBI the date that the Supervisor changed?

- If not, where is the "x" date you mentioned in the original post?

- How does PBI know the "x" date that a supervisor changed for each seller?

Regards,

Nathan

[WinterMist]

@Darwin98 

Not sure if my test data works for what you are trying to do, but here it is in a measure.

NOTES:
- Both Sellers (Jeff & June) switched to a new supervisor on 6/25/2022.

- As you can see, the SALES Table is correctly showing the respective Supervisor 1 BEFORE 6/25, and the respective Supervisor 2 AFTER 6/25.

Let me know if that works for you.

Regards,

Nathan

[Darwin98]

Thanks for your clever response.

I've tried to do it with the same elements you have and it doesn't work 😞 hehehehe

And by any chance can I use the measure in the same table?