Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Business Day Add Using Date Table

Hello

 

Looking for some helpful dax. I need to return a date that is 30 business days from a known date. 

 

I have two tables in my datasource. Table1 is my main data and i'm using an 'OpenDate' as the base/known date.

Table2 is my date table which does contain a 1 or a 0 for business day flag.  This flag is purpose built and has all public holidays and non-working days built into it's logic. 

 

Table2 is joined to the datasource and connected to Table1.[OpenDate].

 

I need to use that column to project 30 days from Table1.[OpenDate].

 

I cannot attach a file due to sensativity. Can anyone help?

 

8 Replies

  • GarryFarrell's avatar
    GarryFarrell
    Advocate III
    7 years ago

    Hi,

     

    Have you seen the NEXTDAY function? You could use this function inconjuction with your flag. You will need to add some logic to skip the "0" dates.

     

     =CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), NEXTDAY('DateTime'[DateKey]))

     

    Garry

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks Garry. I can't really put that solution together with my problem. 

       

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi Anonymous,

         

        Have you solved your problem?

         

        If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

         

        If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

         

        Best  Regards,

        Cherry

  • DaFloDo's avatar
    DaFloDo
    Resolver I
    7 years ago

    hi Anonymous

     

    here is an idea how you could try to solve the problem:

    - have a serial numbering for only business days (I used RANKX to create a new table, but maybe you have better options?)
    - adding a new column that searches for the business day which is 30 ranks higher

    After 30 Business Days = 
var currentRank =  DatesRankedByDay[Rank]
var found = calculate( min(DatesRankedByDay[Date]); All(DatesRankedByDay); DatesRankedByDay[Rank] - currentRank = 30)
return found


    seems to work ... 

     

    regards

     

    Florian

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks DaFloDo, i'm not sure how to create the RankX table. 

       

      Do you think there's a way to use a dateadd function with a condition where the business day flag is 1?

      • DaFloDo's avatar
        DaFloDo
        Resolver I
        7 years ago

        Hi Anonymous,

         

        i added the rank by creating a new calculated table by adding the rank as a new column.

        DatesRankedByDay = ADDCOLUMNS(FILTER(All(Dates), Dates[IsBusinessday]=True), "Rank", RANKX( FILTER(All(Dates), Dates[IsBusinessday]=True), Dates[Date],,ASC))

        I am sorry, I could not solve the problem without the help of this additional column.

         

        best regards

         

        florian