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
Ritaf
Responsive Resident
Responsive Resident

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
ryan_mayu
Super User
Super User

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

Proud to be a Super User!




View solution in original post

Ritaf
Responsive Resident
Responsive Resident

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
ryan_mayu
Super User
Super User

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

Proud to be a Super User!




Ritaf
Responsive Resident
Responsive Resident

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!

@Ritaf 

very glad to hear that. you are welcome.





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

Proud to be a Super User!




amitchandak
Super User
Super User

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

 

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.