Hello Power BI Gurus!!
I'm reaching out for a little nudge in the right direction from your never-ending collection of knowledge! I've tried googling and youtubing for days now trying to come up with a solution. Maybe I found the solution and lacked the requisite knowledge to understand it completely? I'm not sure, but I would greatly appreciate any advice you all could give. Thank you for your time and effort, in advance.
The Context:
I am trying to create a KPI report for order fulfillment. At the moment, I'm wanting to determine what our ship by deadline must be for a huge range of orders. So, depending on the type of the order, we either have 1 or 3 consecutive working days to ship the order.
The Problem:
I have a fact table full of order information. What I want is a calculated column that adds either 1 day or 3 days to the Order Rcv Date depending on whether it's a retail order or a wholesale order. So, I have a conditional column that returns a 1 if it's retail, and a 3 if it's wholesale. The problem is, they must be working days, eg. Monday - Friday. (Note: My weeks are currently option 2, 1mon-7sun) So, If a retail order is received on a friday, if we add 1 it equals Saturday, so we must add 3 to make it Monday. If a wholesale order is received on a thursday, for example, and I add 3, it lands on Sunday, but it's supposed to be that following Tuesday.
Where I'm at now:
I have my fact table, my orders table. I also have a custom date dimension table with fiscal and week ending and quarters and everything you could think of. I also have a column that returns a 1 if it's a workday (a non-weekend and non-holiday), and a 0 if it's a weekend or holiday. I have a relationship between the two tables. I'm just not sure what function I can use in my calculated column for my fact table to complete this. Do I just write a long, nesting, IF formula that basically says "if order rcv date plus one is saturday, add 3, etc. etc.??
So, yeah, that's it. Any wisdom y'all can bestow upon me, I'd be forever grateful. Thanks, guys!
ORDER TABLE EXAMPLE DATA:
Ord Rcv Date Order Received Time Order Type Order Type Deadline must Ship By
| 3/2/2018 | 4:07:24 PM | Retail | 1 | |
| 3/2/2018 | 6:52:56 AM | Retail | 1 | |
| 3/2/2018 | 3:29:31 PM | Retail | 1 | |
| 3/2/2018 | 3:54:57 PM | Retail | 1 | |
| 3/2/2018 | 2:38:27 PM | Wholesale | 3 | |
| 3/2/2018 | 2:40:48 PM | Wholesale | 3 | |
| 3/2/2018 | 4:09:26 AM | Retail | 1 | |
| 3/2/2018 | 6:09:33 AM | Retail | 1 | |
| 3/2/2018 | 6:23:53 AM | Retail | 1 | |
| 3/2/2018 | 6:29:58 AM | Retail | 1 | |
| 3/2/2018 | 6:42:22 AM | Retail | 1 | |
| 3/2/2018 | 9:19:47 AM | Retail | 1 | |
| 3/2/2018 | 10:32:28 AM | Retail | 1 | |
| 3/2/2018 | 11:21:45 AM | Retail | 1 | |
| 3/2/2018 | 11:23:50 AM | Retail | 1 | |
| 3/2/2018 | 1:29:26 PM | Retail | 1 | |
| 3/2/2018 | 3:27:27 PM | Retail | 1 | |
| 3/2/2018 | 10:30:14 AM | Retail | 1 | |
| 3/2/2018 | 8:51:28 AM | Retail | 1 | |
| 3/2/2018 | 9:28:10 AM | Retail | 1 | |
| 3/2/2018 | 9:57:43 AM | Retail | 1 | |
| 3/2/2018 | 3:29:34 PM | Retail | 1 | |
| 3/2/2018 | 11:30:14 AM | Wholesale | 3 | |
| 3/2/2018 | 3:49:10 AM | Retail | 1 | |
| 3/2/2018 | 4:07:17 AM | Retail | 1 | |
| 3/2/2018 | 12:08:35 PM | Retail | 1 | |
| 3/2/2018 | 12:44:51 PM | Retail | 1 | |
| 3/2/2018 | 10:32:31 AM | Wholesale | 3 | |
| 3/2/2018 | 6:07:03 AM | Retail | 1 | |
| 3/2/2018 | 5:22:03 PM | Retail | 1 | |
| 3/5/2018 | 8:28:29 AM | Retail | 1 | |
| 3/5/2018 | 8:30:32 AM | Retail | 1 | |
| 3/5/2018 | 9:03:48 AM | Retail | 1 | |
| 3/5/2018 | 12:11:40 PM | Retail | 1 | |
| 3/5/2018 | 3:16:20 PM | Wholesale | 3 | |
| 3/5/2018 | 9:35:10 AM | Retail | 1 | |
| 3/5/2018 | 10:58:38 AM | Retail | 1 | |
| 3/5/2018 | 11:15:17 AM | Retail | 1 | |
| 3/5/2018 | 12:26:06 PM | Retail | 1 | |
| 3/5/2018 | 4:07:46 PM | Retail | 1 | |
| 3/5/2018 | 4:28:33 PM | Retail | 1 | |
| 3/5/2018 | 7:45:04 PM | Retail | 1 | |
| 3/5/2018 | 6:02:54 AM | Retail | 1 | |
