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