Hello,
I have a factable and a Mastercalendar as a dimensiontable. In the Bookingtable I want to know the processingtime of
two Dates without the weekends and the non-productive Days. With Bookingdate - IncomeDate I would only get the "WrongProcessingTime" but I need the "CorrectProcessingTime".
In the Mastertable I have all the information, but I don't know how to combine these. The "ProductiveDay" column tells me if its
a working day or not. 1 is a ProductiveDay. A Zero tells me its weekend or public holiday.
I'm thinking of a calculated column like Datediff( IncomeDate, BookingDate, Day) , but I don't know how to include the ProductiveDays into that function.
BookingTable
| IncomeDate | | BookingDate | | ID | WrongProcessingTime | | CorrectProcessingTime |
| 2021-09-01 | | 2021-09-01 | | 8888 | 0 | | | 0 | |
| 2021-09-02 | | 2021-09-03 | | 8889 | 1 | | | 1 | |
| 2021-09-03 | | 2021-09-06 | | 8890 | 3 | | | 1 | |
| 2021-09-06 | | 2021-09-07 | | 8891 | 1 | | | 1 | |
| 2021-09-07 | | 2021-09-07 | | 8892 | 0 | | | 0 | |
| 2021-09-08 | | 2021-09-08 | | 8893 | 0 | | | 0 | |
| 2021-09-09 | | 2021-09-14 | | 8894 | 5 | | | 3 | |
| 2021-09-10 | | 2021-09-13 | | 8895 | 3 | | | 1 | |
Mastercalendar:
| Date | Weekday | ProductiveDay |
| 2021-09-01 | Wednesday | 1 |
| 2021-09-02 | Thursday | 1 |
| 2021-09-03 | Friday | 1 |
| 2021-09-04 | Saturday | 0 |
| 2021-09-05 | Sunday | 0 |
| 2021-09-06 | Monday | 1 |
| 2021-09-07 | Tuesday | 1 |
| 2021-09-08 | Wednesday | 1 |
| 2021-09-09 | Thursday | 1 |
| 2021-09-10 | Friday | 1 |
| 2021-09-11 | Saturday | 0 |
| 2021-09-12 | Sunday | 0 |
I really appreciate your help.
Thank you very much in advance.
Best.
