Using dates from 2 tables to identify late transactions

[Ol92]

Hi,

New to Power BI and struggling with comparing event dates against a deadline.  I need to take data similar to below and identify which requests were processed late and by how many days.  On time = requests filed 6th-20th of month processed by 20th, filed 21st to 5th of month, due by 5th. 

I have defined the due dates in my Calendar table but anytime I try to build a Measure or Calculated Column for this, it won't allow me to include the Due Date field or the Process Date field.  I know the fields need to be aggregated before I can include them there and I tried the solution below using Max but it only aggregates one of the fields so I couldn't complete the formula.

check = IF(CALCULATE(MAX(Table4[Release Date]),FILTER(Table4,Table4[Release Date]<=EARLIER(Table4[Delivery Date])))=Table4[Release Date],1,0)

I have also tried using Relate but run into the same problem, at some point I can't retrieve a needed field of data from my fact table.  Clearly there is some basic understanding I'm missing but I've been researching for some time now and I'm not finding a solution.  Any help would be greatly appreciated. 

Req ID	Req Type	Req Date	Process Date
75	Type 1	2/15/2022	2/25/2022
76	Type2	2/14/2022	2/19/2022
77	Type 1	3/21/2022	3/26/2022
78	Type 1	3/30/2022	4/7/2022
79	Type 2	4/9/2022	4/17/2022

[Ol92]

Hi Parry, thanks for the response.  Here is a sample of the summary I'm having trouble with.  

Data
Req ID	Req Type	Req Date	Process Date
75	Type 1	2/15/2022	2/25/2022
76	Type2	2/14/2022	2/19/2022
77	Type 1	3/21/2022	3/26/2022
78	Type 1	3/30/2022	4/7/2022
79	Type 2	4/9/2022	4/17/2022
Summarized
Period Ending	Total Requests	Processed Late
2/20/2022	2	2
3/5/2022	0	0
3/20/2022	0	0
4/5/2022	2	2
4/20/2022	1	0

[parry2k]

@Ol92 from the table you gave, what is the expected output?

[Ol92 · Accepted solution]

I have found a workable solution, though probably not aligned with STAR schema best practice.  I was focused on using calculated columns and measures to bring the two date values together but after stepping away for a while I realized I could just merge the tables to have both dates in the same place. 

From there I made two calculated columns

1) Find the interval between the two dates (Processed Date minus Request Date) and 

2) Use the resulting value to identify the status (<0 = Late)

Now I just need to filter for Late and I can do any rollup I need.  If there's a better way to do this outside of the queries, I would love to hear it but for now at least my report is back on track.