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




tanct's avatar
tanct
Regular Visitor
8 years ago

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)