cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CoreyP Member
Member

HELP; I'm stuck! Adding ONLY working days??

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/20184:07:24 PMRetail1 
3/2/20186:52:56 AMRetail1 
3/2/20183:29:31 PMRetail1 
3/2/20183:54:57 PMRetail1 
3/2/20182:38:27 PMWholesale3 
3/2/20182:40:48 PMWholesale3 
3/2/20184:09:26 AMRetail1 
3/2/20186:09:33 AMRetail1 
3/2/20186:23:53 AMRetail1 
3/2/20186:29:58 AMRetail1 
3/2/20186:42:22 AMRetail1 
3/2/20189:19:47 AMRetail1 
3/2/201810:32:28 AMRetail1 
3/2/201811:21:45 AMRetail1 
3/2/201811:23:50 AMRetail1 
3/2/20181:29:26 PMRetail1 
3/2/20183:27:27 PMRetail1 
3/2/201810:30:14 AMRetail1 
3/2/20188:51:28 AMRetail1 
3/2/20189:28:10 AMRetail1 
3/2/20189:57:43 AMRetail1 
3/2/20183:29:34 PMRetail1 
3/2/201811:30:14 AMWholesale3 
3/2/20183:49:10 AMRetail1 
3/2/20184:07:17 AMRetail1 
3/2/201812:08:35 PMRetail1 
3/2/201812:44:51 PMRetail1 
3/2/201810:32:31 AMWholesale3 
3/2/20186:07:03 AMRetail1 
3/2/20185:22:03 PMRetail1 
3/5/20188:28:29 AMRetail1 
3/5/20188:30:32 AMRetail1 
3/5/20189:03:48 AMRetail1 
3/5/201812:11:40 PMRetail1 
3/5/20183:16:20 PMWholesale3 
3/5/20189:35:10 AMRetail1 
3/5/201810:58:38 AMRetail1 
3/5/201811:15:17 AMRetail1 
3/5/201812:26:06 PMRetail1 
3/5/20184:07:46 PMRetail1 
3/5/20184:28:33 PMRetail1 
3/5/20187:45:04 PMRetail1 
3/5/20186:02:54 AMRetail1 
1 ACCEPTED SOLUTION

Accepted Solutions
CoreyP Member
Member

Re: HELP; I'm stuck! Adding ONLY working days??

Hey guys!! 

 

I finally got a solution to this cRaZy problem!! It was solved in another thread I posted. It can be viewed here:

 

http://community.powerbi.com/t5/Desktop/Possibly-Maybe-a-Lookup-or-something-Check-out-what-I-m-tryi...

21 REPLIES 21
Super User
Super User

Re: HELP; I'm stuck! Adding ONLY working days??

@CoreyP

 

Hi, try with this calculated column:

 

SHipDate =
VAR ShipDate_Step1 =
    CALCULATE (
        MIN ( DateTable[Date] );
        FILTER (
            DateTable;
            DateTable[WorkingDay] = "Yes"
                && DateTable[Date] > Table1[Ord Rcv Date]
        )
    )
RETURN
    IF ( Table1[Order Type  ] = "Retail"; ShipDate_Step1; ShipDate_Step1 + 2 )

Date.png

 

Regards

 

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: HELP; I'm stuck! Adding ONLY working days??

If you create a calendar table that only has working days (just filter your current table) then you could use a variation of my Sequential Quick Measure here:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231

 

And then you would be able to easily get the correct day by just looking up the correct sequential number that is 1 or 3 ahead of the sequential number of your current date. I designed Sequential for just this type of thing.

 

You could also look at my Net Work Days measure, it might be of use.

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


CoreyP Member
Member

Re: HELP; I'm stuck! Adding ONLY working days??

VICTOR! This works MARVELOUSLY! I have no idea what the code is actually doing to make it work, but it works...

 

EXCEPT:  It is not calculating the correct date for some wholesale orders??! I had to tweek your code, presumably to get it to work in the US version, did I take something out I shouldn't have? I also just noticed that after 3/22, the calculations stop working all together with, returning a result of 01/01/1900...

This is the DAX for my calc col:

=================================================================

SHipDate =
VAR ShipDate_Step1 =
CALCULATE (
MIN ( 'Custom Date'[Date] ),
FILTER (
'Custom Date',
'Custom Date'[Workday] = 1
&& 'Custom Date'[Date] > B3[Corrected Ord Rcv Date]
)
)
RETURN
IF ( B3[Order Type] = "Retail", ShipDate_Step1, ShipDate_Step1 + 2 )

===================================================================

And here are the errors where the SHipDate falls on a weekend, which it cannot. 

Order Number	Order Type	Order Type Deadline	Corrected Ord Rcv Date	SHipDate	VERIFICATION2
528415	Wholesale	3	02/28/2018	03/03/2018	WEEKEND DATE
528649	Wholesale	3	02/28/2018	03/03/2018	WEEKEND DATE
529326	Wholesale	3	03/01/2018	03/04/2018	WEEKEND DATE
532874	Wholesale	3	03/07/2018	03/10/2018	WEEKEND DATE
533058	Wholesale	3	03/07/2018	03/10/2018	WEEKEND DATE
533080	Wholesale	3	03/07/2018	03/10/2018	WEEKEND DATE
533641	Wholesale	3	03/08/2018	03/11/2018	WEEKEND DATE
533818	Wholesale	3	03/08/2018	03/11/2018	WEEKEND DATE
533834	Wholesale	3	03/08/2018	03/11/2018	WEEKEND DATE
533978	Wholesale	3	03/08/2018	03/11/2018	WEEKEND DATE
534182	Wholesale	3	03/08/2018	03/11/2018	WEEKEND DATE
534283	Wholesale	3	03/08/2018	03/11/2018	WEEKEND DATE
537700	Wholesale	3	03/14/2018	03/17/2018	WEEKEND DATE
537519	Wholesale	3	03/14/2018	03/17/2018	WEEKEND DATE
537713	Wholesale	3	03/14/2018	03/17/2018	WEEKEND DATE
537803	Wholesale	3	03/14/2018	03/17/2018	WEEKEND DATE
537921	Wholesale	3	03/14/2018	03/17/2018	WEEKEND DATE
537757	Wholesale	3	03/14/2018	03/17/2018	WEEKEND DATE
538502	Wholesale	3	03/15/2018	03/18/2018	WEEKEND DATE
538390	Wholesale	3	03/15/2018	03/18/2018	WEEKEND DATE
538104	Wholesale	3	03/15/2018	03/18/2018	WEEKEND DATE
542246	Wholesale	3	03/21/2018	03/24/2018	WEEKEND DATE
542293	Wholesale	3	03/21/2018	03/24/2018	WEEKEND DATE
542297	Wholesale	3	03/21/2018	03/24/2018	WEEKEND DATE
542307	Wholesale	3	03/21/2018	03/24/2018	WEEKEND DATE
542528	Wholesale	3	03/21/2018	03/24/2018	WEEKEND DATE
542765	Wholesale	3	03/21/2018	03/24/2018	WEEKEND DATE
543133	Wholesale	3	03/22/2018	03/25/2018	WEEKEND DATE
542973	Wholesale	3	03/22/2018	03/25/2018	WEEKEND DATE
543759	Wholesale	3	03/22/2018	03/25/2018	WEEKEND DATE
543183	Wholesale	3	03/22/2018	03/25/2018	WEEKEND DATE

Here are the 01/01/1900's:

Order Number	Order Type	Order Type Deadline	Corrected Ord Rcv Date	SHipDate	VERIFICATION2
543885	Wholesale	3	03/23/2018	01/01/1900	
544399	Wholesale	3	03/23/2018	01/01/1900	
543804	Wholesale	3	03/23/2018	01/01/1900	
543840	Wholesale	3	03/23/2018	01/01/1900	
543894	Wholesale	3	03/23/2018	01/01/1900	
544213	Wholesale	3	03/23/2018	01/01/1900	
544234	Wholesale	3	03/23/2018	01/01/1900	
544374	Wholesale	3	03/23/2018	01/01/1900	
544375	Wholesale	3	03/23/2018	01/01/1900	
544376	Wholesale	3	03/23/2018	01/01/1900	
544377	Wholesale	3	03/23/2018	01/01/1900	
544378	Wholesale	3	03/23/2018	01/01/1900	
544379	Wholesale	3	03/23/2018	01/01/1900	
544380	Wholesale	3	03/23/2018	01/01/1900	
544381	Wholesale	3	03/23/2018	01/01/1900	
544382	Wholesale	3	03/23/2018	01/01/1900	
544384	Wholesale	3	03/23/2018	01/01/1900	
544385	Wholesale	3	03/23/2018	01/01/1900	
544386	Wholesale	3	03/23/2018	01/01/1900	
544387	Wholesale	3	03/23/2018	01/01/1900	
544388	Wholesale	3	03/23/2018	01/01/1900	
544390	Wholesale	3	03/23/2018	01/01/1900	
544391	Wholesale	3	03/23/2018	01/01/1900	
544392	Wholesale	3	03/23/2018	01/01/1900	
544394	Wholesale	3	03/23/2018	01/01/1900	
544395	Wholesale	3	03/23/2018	01/01/1900	
544397	Wholesale	3	03/23/2018	01/01/1900	
544398	Wholesale	3	03/23/2018	01/01/1900	
544400	Wholesale	3	03/23/2018	01/01/1900	
544401	Wholesale	3	03/23/2018	01/01/1900	
544403	Wholesale	3	03/23/2018	01/01/1900	
544031	Wholesale	3	03/23/2018	01/01/1900	
544941	Wholesale	3	03/26/2018	01/01/1900	
544697	Wholesale	3	03/26/2018	01/01/1900	
544769	Wholesale	3	03/26/2018	01/01/1900	
544770	Wholesale	3	03/26/2018	01/01/1900	
544812	Wholesale	3	03/26/2018	01/01/1900	
544813	Wholesale	3	03/26/2018	01/01/1900	
544871	Wholesale	3	03/26/2018	01/01/1900	
545158	Wholesale	3	03/26/2018	01/01/1900	
545572	Wholesale	3	03/26/2018	01/01/1900	
545578	Wholesale	3	03/26/2018	01/01/1900	
545580	Wholesale	3	03/26/2018	01/01/1900	
545621	Wholesale	3	03/26/2018	01/01/1900	

Thoughts??? 

Super User
Super User

Re: HELP; I'm stuck! Adding ONLY working days??

@CoreyP

 

I had alternative way: 

 

Step 1:

 

My Date Table

 

DateT.png

 

The Index Column is:

 

Index =
IF (
    DateTable[WorkingDay] = "Yes";
    COUNTROWS (
        FILTER (
            DateTable;
            DateTable[Date] <= EARLIER ( DateTable[Date] )
                && DateTable[WorkingDay] = "Yes"
        )
    )
)

Step 2:

 

Related DateTable and Table

 

Date2.png

 

Step 3: Duplicate the DateTable (Modeling-New Table)

 

CalendarA = DateTable 

Step 4: A Calculated Column:

 

SHipDate =
VAR Index_Date =
    RELATED ( DateTable[Index] )
RETURN
    IF (
        Table1[Order Type  ] = "Retail";
        CALCULATE (
            MIN ( CalendarA[Date] );
            FILTER ( CalendarA; CalendarA[Index] = Index_Date + 1 )
        );
        CALCULATE (
            MIN ( CalendarA[Date] );
            FILTER ( CalendarA; CalendarA[Index] = Index_Date + 3 )
        )
    )

Dat3.png

 

I'm sure that this would be improve it but this a "Emergency Way".

 

Regards

 

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




CoreyP Member
Member

Re: HELP; I'm stuck! Adding ONLY working days??

Victor,

 

I couldn't get that to work. I've tried another method using logical functions but I think I've found WHY I'm getting some incorrect calculations. Let me show you what I've done and show you the error I've found and hopefully have ya'll shed from light on it for me.

 

TEST4 = 
IF(AND(
        BARRE3[Order Type] = "Retail",
        WEEKDAY(BARRE3[Corrected Ord Rcv Date], 2) = 1),
                BARRE3[Corrected Ord Rcv Date] + 1,

IF(AND(
        BARRE3[Order Type] = "Retail",
        WEEKDAY(BARRE3[Corrected Ord Rcv Date], 2) = 2),
                BARRE3[Corrected Ord Rcv Date] + 1,

IF(AND(
        BARRE3[Order Type] = "Retail",
        WEEKDAY(BARRE3[Corrected Ord Rcv Date], 2) = 3),
                BARRE3[Corrected Ord Rcv Date] + 1,

IF(AND(
        BARRE3[Order Type] = "Retail",
        WEEKDAY(BARRE3[Corrected Ord Rcv Date], 2) = 4),
                BARRE3[Corrected Ord Rcv Date] + 1,

IF(AND(
        BARRE3[Order Type] = "Retail",
        WEEKDAY(BARRE3[Corrected Ord Rcv Date], 2) = 5),
                BARRE3[Corrected Ord Rcv Date] + 3,

IF(AND(
        BARRE3[Order Type] = "Retail",
        WEEKDAY(BARRE3[Corrected Ord Rcv Date], 2) = 6),
                BARRE3[Corrected Ord Rcv Date] + 2,

IF(AND(
        BARRE3[Order Type] = "Retail",
        WEEKDAY(BARRE3[Corrected Ord Rcv Date], 2) = 7),
                BARRE3[Corrected Ord Rcv Date] + 1,

IF(AND(
        BARRE3[Order Type] = "Wholesale",
        WEEKDAY(BARRE3[Corrected Ord Rcv Date], 2) = 1),
                BARRE3[Corrected Ord Rcv Date] + 3,

IF(AND(
        BARRE3[Order Type] = "Wholesale",
        WEEKDAY(BARRE3[Corrected Ord Rcv Date], 2) = 2),
                BARRE3[Corrected Ord Rcv Date] + 3,

IF(AND(
        BARRE3[Order Type] = "Wholesale",
        WEEKDAY(BARRE3[Corrected Ord Rcv Date], 2) = 3),
                BARRE3[Corrected Ord Rcv Date] + 5,

IF(AND(
        BARRE3[Order Type] = "Wholesale",
        WEEKDAY(BARRE3[Corrected Ord Rcv Date], 2) = 4),
                BARRE3[Corrected Ord Rcv Date] + 5,

IF(AND(
        BARRE3[Order Type] = "Wholesale",
        WEEKDAY(BARRE3[Corrected Ord Rcv Date], 2) = 5),
                BARRE3[Corrected Ord Rcv Date] + 5,

IF(AND(
        BARRE3[Order Type] = "Wholesale",
        WEEKDAY(BARRE3[Corrected Ord Rcv Date], 2) = 6),
                BARRE3[Corrected Ord Rcv Date] + 4,

IF(AND(
        BARRE3[Order Type] = "Wholesale",
        WEEKDAY(BARRE3[Corrected Ord Rcv Date], 2) = 7),
                BARRE3[Corrected Ord Rcv Date] + 3,

0 ))))))))))))))
TEST4 validation = FORMAT(WEEKDAY(BARRE3[TEST4], 2 ), "DDDD")
Corrected Ord Rcv Date	Order Type	TEST4	TEST4 validation
2/26/2018	Retail	2/27/2018	Monday
2/26/2018	Wholesale	3/1/2018	Wednesday
2/27/2018	Retail	2/28/2018	Tuesday
2/27/2018	Wholesale	3/2/2018	Thursday
2/28/2018	Retail	3/1/2018	Wednesday
2/28/2018	Wholesale	3/5/2018	Sunday
3/1/2018	Retail	3/2/2018	Thursday
3/1/2018	Wholesale	3/6/2018	Monday
3/2/2018	Retail	3/5/2018	Sunday
3/2/2018	Wholesale	3/7/2018	Tuesday
3/5/2018	Retail	3/6/2018	Monday
3/5/2018	Wholesale	3/8/2018	Wednesday
3/6/2018	Retail	3/7/2018	Tuesday
3/6/2018	Wholesale	3/9/2018	Thursday
3/7/2018	Retail	3/8/2018	Wednesday
3/7/2018	Wholesale	3/12/2018	Sunday
3/8/2018	Retail	3/9/2018	Thursday
3/8/2018	Wholesale	3/13/2018	Monday
3/9/2018	Retail	3/12/2018	Sunday
3/9/2018	Wholesale	3/14/2018	Tuesday
3/12/2018	Retail	3/13/2018	Monday
3/12/2018	Wholesale	3/15/2018	Wednesday
3/13/2018	Retail	3/14/2018	Tuesday
3/13/2018	Wholesale	3/16/2018	Thursday
3/14/2018	Retail	3/15/2018	Wednesday
3/14/2018	Wholesale	3/19/2018	Sunday
3/15/2018	Retail	3/16/2018	Thursday
3/15/2018	Wholesale	3/20/2018	Monday
3/16/2018	Retail	3/19/2018	Sunday
3/16/2018	Wholesale	3/21/2018	Tuesday
3/19/2018	Retail	3/20/2018	Monday
3/19/2018	Wholesale	3/22/2018	Wednesday
3/20/2018	Retail	3/21/2018	Tuesday
3/20/2018	Wholesale	3/23/2018	Thursday
3/21/2018	Retail	3/22/2018	Wednesday
3/21/2018	Wholesale	3/26/2018	Sunday
3/22/2018	Retail	3/23/2018	Thursday
3/22/2018	Wholesale	3/27/2018	Monday
3/23/2018	Retail	3/26/2018	Sunday
3/23/2018	Wholesale	3/28/2018	Tuesday
3/26/2018	Retail	3/27/2018	Monday
3/26/2018	Wholesale	3/29/2018	Wednesday
CoreyP Member
Member

Re: HELP; I'm stuck! Adding ONLY working days??

You'll notice that 03/05/2018 shows as Sunday in the test 4 validation column, however, March 5th is a MONDAY.

 

What's going on here?

CoreyP Member
Member

Re: HELP; I'm stuck! Adding ONLY working days??

**UPDATE**

 

Okay... So, I got your code to work; I had accidentally referenced an incorrect column, which is why it didn't work. Also, I realized that MAYBE the days were getting effed up because my "Custom Date" table used CALENDAR(FIRSTDATE(Order Rcv Date), LASTDATE...etc.). I just switch that to CALENDAR, with a hardcoded date range.

 

ALSO... my "TEST4" nesting IF statements provides an IDENTICAL match to your column. HOWEVER, my validation is still showing some ship by dates landing on a SUNDAY when the actual date is not. eg.) 3/26/18, 3/5/18, 3/22/18,

 

I have no idea why...

CoreyP Member
Member

Re: HELP; I'm stuck! Adding ONLY working days??

***UPDATE 1:18PM***

 

I JUST GOT IT TO WORK! BOOYAH!

 

For my Order Rcv Date, I had been referencing a conditional column I had previously created to account for orders that dropped on the weekend, and would change it to the following monday, this was called my "Corrected Ord Rcv Date."

 

I just thought that might be causing issues because my TEST4 column also has rules for if the Rcv Date falls on a weekend, so it was like double condition or something. So, I just changed my TEST4 column reference to the actual Order Received Date, and then I changed my TEST4 Validation column ..

 

FROM:

TEST4 validation = FORMAT(WEEKDAY(BARRE3[TEST4], 2), "DDDD")

TO:

 

TEST4 validation = FORMAT(BARRE3[TEST4], "DDDD")

 

...AAAnnnnnd, whaddya know?! I don't get a single weekend date in my TEST4 column.

CoreyP Member
Member

Re: HELP; I'm stuck! Adding ONLY working days??

Ahhh, Victor! After getting my IF statements way to work, I realized that it only accounts for weekends and not holidays, so I'm going to have to try your code out again and see if I can get that to work!