New Date Based on column in different table

[Jmccoy]

I have a Required delivery date in my Sales table and then a small table that is populated with a lead time from my scheduler. This is in the form of the number of weeks we are currently scheduling before delivery, this can change from week to week. I am wanting to subtract the current lead time from the the Required delivery date provided by the customer, to display per job and show a date the project must be fully released to the plant to hit this date. 

The Sales Table has many of rows with all of our promised jobs and details. The lead time table is just one row with current lead time that is updated by the scheduler. This usually ranges from 9-15 week lead times. 

[Jmccoy]

Something like this but in lieu of the - 84 days it would be the lead times in the other column. FDRD = DATEADD(Dashboard_Sales2[ReqDel].[Date], -84,DAY)

[v-lid-msft · Accepted solution]

Hi @Jmccoy ,

What is the relationship between two tables, such as if it based on jobid column, we can use the following formula:

FDRD =
DATEADD (
    Dashboard_Sales2[ReqDel].[Date],
    - CALCULATE (
        SUM ( 'Table 2'[Lead Time] ),
        'Table 2',
        'Table 2'[jobid] in Distinct(Dashboard_Sales2[jobid])
    ),
    DAY
)

If it doesn't meet your requirement,  Could you please provide a mockup sample based on fake data or describle the fields of each tables and the relations between tables simply? It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to OneDrive For Business and share the link here.

Best regards,