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




v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
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




Emanuel's avatar
Emanuel
Helper I
4 years ago

v-huizhn-msft Is it possible to add also the time for the business days? Like I have the date 1-Oct-2021 9:39 PM and I need to add 5 business days to it but the result will look like 8-Oct-2021 12:00 AM. Instead I need the time to show the actual end of business day, like 6pm for example. Is it possible based on your solution?

Thanks

Emanuel