Calculate Product during Support Call

[CrownWorker]

Hello,

I have 2 very large tables that bog down SQL when I attempt to create subqueries to calculate which product was in place in our billing system during a call in the support system. I am hoping that DAX can preform this function as a calculated column or measure more efficiently.

I have created a mock up of the important fields and left my feeble attempts to calculate this. I know it isnt working because the record you will see on Sunday August 20th 2017 should be the record that is attached to Monday August 21 2017 and instead it is showing thursday August 17th 2017

Here are the tables I have made up for a mock up

This is my feeble attempt at the calculation

Here is another view of the tables being worked with

[CrownWorker]

I relalize that I only put in one service number, of course we have many service numbers and the calculation will need to match the service number then the max date that is less than the support call date and return the product.

[Anonymous]

@CrownWorker,

Do you make a relationship between the two tables using Index column?

If so, the index 6 in Billing Record table has no related row in Support Record table, thus, when you calculate lastdate column using  CALCULATE(LASTNONBLANK('Billing Record'[Billing Date],1)), you get 8/17/2017 rather than 8/20/2017 for the 8/21/2017 support date,  this is the reason why you don't get expected result in your table visual.


Regards,
Lydia