Last moving 3 month Average
Hi all, I have the following data set sample: job_name delivery_starting_date delivery_starting_datetime delivery_ending_datetime JobDurationYesterday A 07/12/2022 07/12/2022 1:00 07/12/2022 2:30 1,5 B 07/12/2022 07/12/2022 7:00 07/12/2022 7:30 0,5 C 07/12/2022 07/12/2022 6:00 07/12/2022 7:00 1 A 06/12/2022 06/12/2022 0:00 06/12/2022 1:00 1,0 B 06/12/2022 06/12/2022 7:00 06/12/2022 9:00 2,0 C 06/12/2022 06/12/2022 7:00 06/12/2022 10:00 3 A 05/12/2022 05/12/2022 8:00 05/12/2022 10:00 2 In my current page, what I want to see is all the deliveries that started after yesterday's date at 18:00:00, for which I have a Dim_Table like the following table. It is related with the first table by delivery_starting_date and Date: Date Yesterday 05/12/2022 TODAY()-1 18:00:00 06/12/2022 TODAY()-1 18:00:00 07/12/2022 TODAY()-1 18:00:00 08/12/2022 TODAY()-1 18:00:00 09/12/2022 TODAY()-1 18:00:00 10/12/2022 TODAY()-1 18:00:00 11/12/2022 TODAY()-1 18:00:00 What I would like to obtain is the Average Job Duration of the Last Three months (from date: yesterday at 18:00:00 minus three months) by job name, so I obtain the following table: job_name delivery_starting_date delivery_starting_datetime JobDurationYesterday Avg Job Duration Last Three Months A 07/12/2022 07/12/2022 1:00 1,5 1,5 B 07/12/2022 07/12/2022 7:00 0,5 1,3 C 07/12/2022 07/12/2022 6:00 1 2 Could someone help me out, please? Thanks in advance for your help! BR, Sara
