Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
Proud to be a Super User!
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 :
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.
Proud to be a Super User!
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 :
very glad to hear that. you are welcome.
Proud to be a 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...
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |