Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CoreyP
Solution Sage
Solution Sage

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

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...

View solution in original post

21 REPLIES 21
Greg_Deckler
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

or @Greg_Deckler, Can you help further explain how to get your sequential to work? Your post didn't really explain it enough for my novice understanding. 

 

I do already have two columns in my date table, one column that returns a 0 for non-workdays, and a 1 for workdays. Another is an index column that sequentially numbers the workdays of my date table. So, any non-workdays are blank and the sequence resumes afterwards.

Vvelarde
Community Champion
Community Champion

@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

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??? 

Vvelarde
Community Champion
Community Champion

@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

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!

@Vvelarde

 

I'm stuck again. Haha. So, I realized my IF statements solution only accounted for weekends and not holidays. ERG.

 

So, here's what I've got:

 

Date Table = 'Custom Date'

This has all the good stuff: date, fiscal periods, month names, week ending, day of the week, weeknum, the whole shabang. I also have a column called Workday, which returns a 1 if it's not a weekend or holiday, and returns a 0 if it's a weekend OR a holiday. I also have a column named Index, which assigns a sequential number to every workday. So monday 1, tues 2, wed 3, etc, saturday and sunday are null (blank), as are holidays. 

 

Fact Table = B3 Orders

A table with all orders in a given time period. So, it has order received date, closed date, etc. etc. If the order type is retail, i need to add 1 business day to the order received date. If it's wholesale, I need to add 3 business days to the order received date. I have a relationship between my date column in date table and my order received date column in my orders table. 

 

I tried using your previously mentioned solution:

SHipDateTEST = 
VAR Index_Date =
    RELATED ( 'Custom Date'[Index] )
RETURN
    IF (
        BARRE3[Order Type] = "Retail",
        CALCULATE (
            MIN ( 'Custom Date'[Date] ),
            FILTER ( 'Custom Date', 'Custom Date'[Index] = Index_Date + 1 )
        ),
        CALCULATE (
            MIN ( 'Custom Date'[Date] ),
            FILTER ( 'Custom Date', 'Custom Date'[Index] = Index_Date + 3 )
        )
    )

Most notably, the incorrect date it returns is 12/26/2017 (which is the FIRST date in my date table)

 

Please... I know we are SO close.

Is there no DAX function that's basically NEXTVALUE or something like that?? I dunno.. I'm frustrated.

Vvelarde
Community Champion
Community Champion

@CoreyP

 

Hi, the Index column will be a sequential number

 

Index.png

 

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

In your case instead of using "Yes" replace with 1

 

Regards

 

Victor




Lima - Peru

@Vvelarde,

 

Right, I've already done that. That is how I have a working index column in my date table. I have this code:

Index = 
IF (
    'Custom Date'[Workday] = 1,
    COUNTROWS (
        FILTER (
            'Custom Date',
            'Custom Date'[Date] <= EARLIER ( 'Custom Date'[Date] )
                && 'Custom Date'[Workday] = 1
        )
    )
)

 

What I'm trying to do is have a calc col in my Orders table, that, depending on the order type, returns the order received date + 1 (working day) or + 3 (working days)...

Vvelarde
Community Champion
Community Champion

@CoreyP

 

Do you note that i have 2 calendar tables?

 

Review this PBIX. Maybe you have a different structure. Smiley Happy

 

Regards

 

Victor




Lima - Peru

Yes, it does not work. I'm not sure why. I've tried it with several configurations. 

 

Let me ask yall this: 

 

So, I've got a working day date index, right? So only working days are numbered. Can't I do something like, 

 

If an order is retail, then ship date equals the related date value of the date index of the order date + 1 

and

If an order is wholesale, then ship date equals the related date value of the date index of the order date + 3??

 

Does this make sense? 

Vvelarde
Community Champion
Community Champion

@CoreyP

 

I reccomended review the calculated column that i use.

 

In VAR i Use The DateTable ( This is Related to Orders or other Name of your Table)

In CALCULATE And FILTER i use the other CalendarTable (Not related to Any Table)

 

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 )
        )
    )

 




Lima - Peru

Yes, thank you so much for clarification, but that results in a lot of blank values...

@Vvelarde@Greg_Deckler,

 

So, here's what I'm thinking now...

 

I want to use the index column I created in my date table to return the correct ship by date. I've written the following formula:

LOOKUP = 
        IF(BARRE3[Order Type] = "Retail",
                LOOKUPVALUE('Custom Date'[Date], 'Custom Date'[Index], BARRE3[DateIndex]+1),
        IF(BARRE3[Order Type] = "Wholesale", 
                LOOKUPVALUE('Custom Date'[Date], 'Custom Date'[Index], BARRE3[DateIndex]+3),
0))

However, it doesn't account for blank values, ie) weekends and holidays. Rather than using "[DateIndex] + 1 or 3," is there a function that will return the next non-blank value? Or the next sequential number?

 

Thanks again for all your efforts on this, guys. I know it's a tricky situation. If only MS would have created a WORKDAY formula for DAX...

FIRSTNONBLANK might do the trick.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler,

 

Thanks for the response! I'll look into that here shortly! 

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...

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

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?

**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...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.