cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

Moving to the next working day column

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Moving to the next working day column

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

1.PNG

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

1.PNG

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.

 

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

Highlighted
Post Patron
Post Patron

Re: Moving to the next working day column

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!

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Moving to the next working day column

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User II
Super User II

Re: Moving to the next working day column

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

1.PNG

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

1.PNG

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.

 

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

Highlighted
Post Patron
Post Patron

Re: Moving to the next working day column

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!

View solution in original post

Highlighted
Super User II
Super User II

Re: Moving to the next working day column

@Ritaf 

very glad to hear that. you are welcome.

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors