Forum Discussion
Using dates from 2 tables to identify late transactions
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 |
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.
3 Replies
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.
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
