Moving to the next working day column

[Ritaf]

Hi All,

I have a data model with Purchase orders transactions and a Dates table.

I want to create a "delivery day Calendar" for the warehouse manager.
Part of the Delivery dates in the transactions table is weekends/holidays,

when it happens I want the delivery date to move forward to the next workday.

How can I do it?

(Date table includes a column with a workday indicator (1 - workday, 0- weekend/holiday).
Thanks a lot, Rita

[ryan_mayu · Accepted solution]

@Ritaf 

not sure if I understand your request clearly.

1. in your date table, you need to calculate a isweekday column

something like

if(WEEKDAY('Dates'[Date],1) in {1,7},"NO","Yes")

What's more, I also set 2020/1/1 as not weekday, I think it's a holiday.

2. then in your fact table ,you can calculate the next workday.

next workday = 
MINX(FILTER('Dates','Table'[purchaseorderdate]<'Dates'[Date]&&'Dates'[weekday]="Yes"),'Dates'[Date])

you can see 2020/1/1,2020/1/4,2020/1/5 are holidays and weekends. All of them are skipped.

Hope this is helpful.

[ryan_mayu · Accepted solution]

@Ritaf 

not sure if I understand your request clearly.

1. in your date table, you need to calculate a isweekday column

something like

if(WEEKDAY('Dates'[Date],1) in {1,7},"NO","Yes")

What's more, I also set 2020/1/1 as not weekday, I think it's a holiday.

2. then in your fact table ,you can calculate the next workday.

next workday = 
MINX(FILTER('Dates','Table'[purchaseorderdate]<'Dates'[Date]&&'Dates'[weekday]="Yes"),'Dates'[Date])

you can see 2020/1/1,2020/1/4,2020/1/5 are holidays and weekends. All of them are skipped.

Hope this is helpful.

[Ritaf · Accepted solution]

Hi Ryan,
It almost worked but had a little problem. It added 1 workday to all delivery dates include correct workdays.
I added "if", and it solved this issue.
Finaly my dax is :

next workday calculated delievery = IF(RELATED('PBI DWH_Dim_Datetime_view'[workDay])=1,'PBI DWH_Fact_OrdersPurchase_Items_view'[CalculatedDelieveryDate],

MINX(FILTER('PBI DWH_Dim_Datetime_view','PBI DWH_Fact_OrdersPurchase_Items_view'[CalculatedDelieveryDate]<'PBI DWH_Dim_Datetime_view'[copyDateKey]&&'PBI DWH_Dim_Datetime_view'[workDay]=1),'PBI DWH_Dim_Datetime_view'[copyDateKey]))

Thank you very much, Rita!

[ryan_mayu]

@Ritaf 

very glad to hear that. you are welcome.

[amitchandak]

@Ritaf , I have a blog, where I created Rank on Working day to get the Next workday using Rank. See if that can help you

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...