Forum Discussion
Using dates from 2 tables to identify late transactions
- 4 years ago
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 |