Date Minus Days and exlude non working days

[A48skzz]

Hello,

i have a table with column "requested delivery date" and column "transit time" (which vary depending on the transportation method from 1 up to 40 days).

Now i want to calculate the real shipment date where the delivery must be shipped to be meet the "requestes delivery date"!

So "requested delivery date" minus "transit days" which also exlude weekends and bank holidays = "real shipment date".

I created already a separate table in excel and uploaded it into PowerBI with weekend days and bank-holidays, where 

- working days = 1

- weekend days = 0

- bank holiday days = 0

But now I don't know how to create the formula?

Does anyone have an idea?

Thanks in advance!

Andy

[PhilipTreacy]

Hi @A48skzz 

Modify the DAX for Real Shipment Date to this

Real Shipment Date = 

VAR DelDate = [Requested Delivery Date] - ([Transit Time] + CALCULATE(COUNTROWS('DateTable'), DATESBETWEEN('DateTable'[Date], ('Table'[Requested Delivery Date] - 'Table'[Transit Time]) , 'Table'[Requested Delivery Date]), 'DateTable'[IsWorkingDay] = False , ALL('Table')))

RETURN

IF(WEEKDAY(DelDate) = 1 , DelDate - 2, IF(WEEKDAY(DelDate) = 7, DelDate - 1, DelDate))

regards

Phil

[A48skzz]

Hi Phil,

any idea how to solve this issue with the bank holiday? See my latest post from yesterday!

thanks in advance!

BR

Andreas

[v-kelly-msft]

Hi  @A48skzz ,

Sorry for the late reply!

Modify the expression as below:

Real Shipment Date = 

VAR DelDate = [Requested Delivery Date] - ([Transit Time] + CALCULATE(COUNTROWS('DateTable'), DATESBETWEEN('DateTable'[Date], ('Table'[Requested Delivery Date] - 'Table'[Transit Time]) , 'Table'[Requested Delivery Date]),FILTER('DateTable', 'DateTable'[IsWorkingDay] = TRUE() && 'DateTable'[Is holiday]=0)))

RETURN

IF(WEEKDAY(DelDate) = 1 , DelDate - 2, IF(WEEKDAY(DelDate) = 7, DelDate - 1, DelDate))

Best Regards,
Kelly

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

[A48skzz]

Hi Kelly,

sorry it is still not working properly.

Any further idea?

Appreciate your help!!

BR

Andreas

[v-kelly-msft]

Hi  @A48skzz ,

Sorry for the late reply.

Create a column as below:

Real shipment date1 = 
var date1='Table'[Requested Delivery Date]-'Table'[Transit Time]
var weekenddays=CALCULATE(COUNTROWS('DateTable'),FILTER('DateTable','DateTable'[Date]>=date1&&'DateTable'[Date]<='Table'[Requested Delivery Date]&&'DateTable'[IsWorkingDay]=0))+0
var _days=CALCULATETABLE(VALUES('DateTable'[Date]),FILTER('DateTable','DateTable'[Date]>=date1&&'DateTable'[Date]<='Table'[Requested Delivery Date]&&'DateTable'[IsWorkingDay]=0))
var holidays=CALCULATE(COUNTROWS('DateTable'),FILTER('DateTable','DateTable'[Date]>=date1&&'DateTable'[Date]<='Table'[Requested Delivery Date]&&'DateTable'[Is holiday]=1&&NOT('DateTable'[Date]) in _days))+0
Return
date1-weekenddays-holidays

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[A48skzz · Accepted solution]

Hi Kelly,

thanks for your reply. Unfortunately it was still not working as expected but I have found another solution which works perfect.

I searched a bit in the forum and found a similiar code for my solution here:

https://community.powerbi.com/t5/Desktop/Add-working-days-to-a-submitted-date/m-p/1630285

Real Shipment Date =  
VAR __numberofDays = 'Table'[TransitTime]

VAR __orderDate = 'Table'[Requested Delivery Date] 

VAR __dateTable =     CALCULATETABLE (         VALUES ( 'DateTable' ),         'DateTable'[Workday] = TRUE,         'DateTable'[Date] <= __orderDate     ) 

VAR __dateTableWithRank =     ADDCOLUMNS (         __dateTable,         "@Rank", RANKX ( __dateTable, [Date],,DESC, Dense )     ) 
RETURN 

MAXX (     FILTER (         __dateTableWithRank,         [@Rank] = __numberofDays + 1    ),     [Date]  ) ​

However,

thanks again

Andreas

[v-kelly-msft]

Hi  @A48skzz ,

Thanks for your sharing.🙂

Best Regards,
Kelly

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

[A48skzz]

Hi Phil,

perfect! This works pretty good for the weekends.

Last not but least now I further need to exlude the bank holidays like Good Friday 2nd of April, Easter Monday 5th of April etc. from the real shipment date.

I tried with another IF + lookup (Is Working Day Table) formula, but this wasn't working.

Do you have a further idea?

thanks a lot for your help, appreciated!

Andreas

[v-kelly-msft]

Hi @A48skzz,

First create a column in date table:

Is holiday = 
VAR Holidays = 
       { DATE(2017,01,01),DATE(2018,01,01),DATE(2019,01,01),DATE(2020,01,01),DATE(2021,01,01), DATE(2021,01,26),DATE(2021,04,02),DATE(2021,04,04),DATE(2021,04,05),DATE(2021,04,26),DATE(2021,12,24),DATE(2021,12,25)
        }
        Return
        IF('DateTable'[Date] in Holidays,1,0)

Put all the holiday dates in the list.

Then create a column in Table as below:

Real Shipment Date = 

VAR DelDate = [Requested Delivery Date] - ([Transit Time] + CALCULATE(COUNTROWS('DateTable'), DATESBETWEEN('DateTable'[Date], ('Table'[Requested Delivery Date] - 'Table'[Transit Time]) , 'Table'[Requested Delivery Date]), 'DateTable'[IsWorkingDay] = False ,'DateTable'[Is holiday]=0, ALL('Table')))

RETURN

IF(WEEKDAY(DelDate) = 1 , DelDate - 2, IF(WEEKDAY(DelDate) = 7, DelDate - 1, DelDate))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[A48skzz]

Hi all,

I'm still facing to the issue that the new (real shipment date) is falling on bank holidays.

Bank holiday and working day list is maintained correctly.

I have the same issue for 2nd April 2021.

Any clue how to slove this?

Thanks & BR

Andy

[A48skzz]

+ add:

It doesn't really matter if I use:

or

or

2nd of April and 5th of April our always given back as real working day for the real shipment day!

[PhilipTreacy]

Hi @A48skzz 

Download example PBIX file

You need to create a Date Table in your model with columns indicating working/non-working days.

You can then work out the number of non-working days between the Requested Delivery Date and Requested Delivery Date - Transit Time

For example if the Requested Delivery Date is 2 Jan 2021 and the Transit Time is 8 days, then the delivery must depart on 21 Dec 2020.

In the attached file I have indicated that Sat and Sun are non-working days.  You also need to set up what dates are holidays where you live.  I'm in Australia so I've set up this list (it's not complete, just for example)

VAR Holidays = 
    DATATABLE (
        "HolidayDate", DATETIME,
        "HolidayName", STRING,
        {
            { "2017-01-01", "New Year's Day" },
            { "2018-01-01", "New Year's Day" },
            { "2019-01-01", "New Year's Day" },
            { "2020-01-01", "New Year's Day" },
            { "2021-01-01", "New Year's Day" },
            { "2021-01-26", "Australia Day" },
            { "2021-04-02", "Good Friday" },
            { "2021-04-04", "Easter Sunday" },
            { "2021-04-05", "Easter Monday" },
            { "2021-04-26", "ANZAC Day" },  //25th is Sunday
            { "2021-12-25", "Christmas Eve" },
            { "2021-12-25", "Christmas Day" }
        })

You need to edit the code for the DateTable to insert your own holiday dates.

With this done you can create a column like this

Real Shipment Date = [Requested Delivery Date] - ([Transit Time] + CALCULATE(COUNTROWS('DateTable'), DATESBETWEEN('DateTable'[Date], 'Table'[Requested Delivery Date] - 'Table'[Transit Time], 'Table'[Requested Delivery Date]), 'DateTable'[IsWorkingDay] = False , ALL('Table')))

Which gives this result 

regards

Phil

[A48skzz]

Hi Phil,

thanks for your reply any the pbix file with the code.

This solves my question already, but there is still on little issue that the "real shipment days" could fall on Saturday's and Sunday's.

I used your pbix data and added my data from my origin file, please see below outcome.

So usuallly working days are only from MO-FR, so the shipment must also be done within MO-FR.

I also checked my Working-Day file which is correct maintained.

Is there any chance to exclude also Saturday's and Sunday's for the "real shipment date" ?

Appreciate your help!

Thanks

Andy