Forum Discussion

huguest's avatar
huguest
Advocate II
9 years ago
Solved

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 =...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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




isamchakur's avatar
isamchakur
Frequent Visitor
6 years ago
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