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




Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi,

 

I am facing some challenges: the formula for adding 'n' days doesn't show a future date or the date which is the last row in the 'Date' column. 

If the last date 24-JUN-2021, then adding 5 days should show 1-JUL-2021, but the last entry which I am getting in 'Add 5 working days' column is 17-JUN-2021.

Attaching the screenshot for your reference. 

Can you please help me with some tips?

  • Emanuel's avatar
    Emanuel
    Helper I
    5 years ago

    Anonymous I had the same problem and is related to the end date you have configured in  you calendar. Add a year to your end date calendar and this is fixed.
    Hope this helps