Forum Discussion
Add working days to a date
Hello, I am fairly new to Power BI and to this forum (first post). I need to add a column that adds a fixed number (e.g. 15) of working days to a date that is already in my table. In Excel, I use =WORKDAY([Date],15,[Holidays]), but I cannot figure out how to get the equivalent output in Power BI. I am able to add a certain number of days (using Date.AddDays([Date], 15), but would really need to add working days. Any help would be appreciated.
hu9uest.
Hi huguest,
After research and test, there is no a function used to achieve same requirement in Power BI. You can follow the following to solutions.
1.You can calculated them in Excel, then load the data to PowerBI.
2. Create a Canlendar table. The start_date is your min(facttable[date]), and end_date your max(facttable[date]).
CALENDAR(<start_date>, <end_date>)
In Calendar table, create calculated column to display the day if it is work day, rank all working days, get the result based on calculated columns. For example, I add futher 10 working days.Identify = IF(OR(WEEKDAY(Canlendar[Date])=1, WEEKDAY(Canlendar[Date].[Date])=7),0,1) Rank1 = RANKX(FILTER(Canlendar,Canlendar[Identify]=1),Canlendar[Date],,ASC) Add 10 woring days = LOOKUPVALUE(Canlendar[Date],Canlendar[Identify],1,Canlendar[Rank1],Canlendar[Rank1]+10)
Create a relationship batween Calendar and your fact table. Use the related function to get the result in fact table.
Result=RELATED(Calendar[Add 10 woring days])
If you have other issues, please feel free to ask.
Best Regards,
Angelia
13 Replies
Hi huguest,
After research and test, there is no a function used to achieve same requirement in Power BI. You can follow the following to solutions.
1.You can calculated them in Excel, then load the data to PowerBI.
2. Create a Canlendar table. The start_date is your min(facttable[date]), and end_date your max(facttable[date]).
CALENDAR(<start_date>, <end_date>)
In Calendar table, create calculated column to display the day if it is work day, rank all working days, get the result based on calculated columns. For example, I add futher 10 working days.Identify = IF(OR(WEEKDAY(Canlendar[Date])=1, WEEKDAY(Canlendar[Date].[Date])=7),0,1) Rank1 = RANKX(FILTER(Canlendar,Canlendar[Identify]=1),Canlendar[Date],,ASC) Add 10 woring days = LOOKUPVALUE(Canlendar[Date],Canlendar[Identify],1,Canlendar[Rank1],Canlendar[Rank1]+10)
Create a relationship batween Calendar and your fact table. Use the related function to get the result in fact table.
Result=RELATED(Calendar[Add 10 woring days])
If you have other issues, please feel free to ask.
Best Regards,
Angelia
This really helped me! Thank you very much
Hi Angelia,
Could you elaborate more on the below function (ASC?) or share me the image of the "rank 1" the full complete dax code (as what you did for Add 10 woring days ) because I stucked on this step,thanks.
Rank1 = RANKX(FILTER(Canlendar,Canlendar[Identify]=1),Canlendar[Date],,ASC)
- Hello, I tried something that worked well to if you do not consider the Hollidays during the week: DayofWeek = Date.DayOfWeek([Date] 3WorkDaysBefore = if [DayofWeek ]=1 then Date.AddDays([Date],-5) else if [DayofWeek ]=2 then Date.AddDays([Date],-5) else if [DayofWeek ]=3 then Date.AddDays([Date],-5) else if [DayofWeek ]=4 then Date.AddDays([Date],-3) else if [DayofWeek ]=5 then Date.AddDays([Date],-3) else null
How i can exclude Holiday list in same formula?
