PowerBI Complex Calculated Column with multiple TOPN loops

Question

Using the information below I need to create a new table in DAX called Table.

My problem is that the below measure for the "new value" column is only returning the TOPN audit record for the saleID (as long as the audit record can contain the sale date between the "changed date" and "valid until" date.). This filter should be adjusted to only detect whether the "changed Date" occurred on or after the "Sale Date". The "Valid Until" column shouldn't factor into this calculation.

new value = 
VAR CurrentContractDate = [Sale Date]
RETURN
VAR RespEmp =
            TOPN (
                1,
                DynamicsAudit,
                IF (
                    CurrentContractDate <= DynamicsAudit[Valid Until]
                        && CurrentContractDate >= DynamicsAudit[Changed Date], //Check, whether there is matching date
                    DATEDIFF ( DynamicsAudit[Changed Date], DynamicsAudit[Valid Until], DAY ), //If so, rank matching locations (you may want to employ a different formula)
                    MIN (  //If the location is not matching, calculate how close it is (from both start and end date)
                        ABS ( DATEDIFF ( CurrentContractDate, DynamicsAudit[Changed Date], DAY ) ),
                        ABS ( DATEDIFF ( CurrentContractDate, DynamicsAudit[Valid Until], DAY ) )
                    ) + 1000000 //Add a discriminating factor in case there are matching rows that should be favoured over non-matching.
                ), 1
            )
            RETURN
            SELECTCOLUMNS( RespEmp,"ResponsibleEmployee", [new value] )

So what does such a complex filter look like?

Answer 1

new value = 
VAR sale = [SaleID]
VAR saleDate = [Sale Date]
VAR tbl = SUMMARIZE(FILTER(DynamicsAudit, DynamicsAudit[SaleID] = sale && DynamicsAudit[Changed Date] >= saleDate),DynamicsAudit[new value])
VAR tbl2 = NATURALINNERJOIN(
            FILTER(DbEmployees, DbEmployees[StartDate]<=saleDate && DbEmployees[EndDate] >= saleDate),
            CALCULATETABLE(DynamicsContacts, TREATAS(tbl, DynamicsContacts[EmployeeID])))

RETURN MAXX(TOPN(1,tbl2, IF(DbEmployees[Position] = "clerk", 1,2), ASC), DynamicsContacts[EmployeeID])

First table expression gets you the list of employees that qualify.

Second table expression joins the Employee (filtered by the ones that had a contract active during the sale date) and Contacts (filtered by the ones that qualify)

Finally do a Top 1 where it picks first the Clerks and then the Managers.