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'...
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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks DaFloDo, can you clarify - should i be creating a new column in the date table with this code, or, should i be choosing 'new table' and then using this code. 

       

      When i add this as a new column in the Dim_Date, this is the error i get:

      The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

       

       

      When i choose new table the error is this:

      "This expression refers to a Column object named 'Dim_Date[DatesRankedByDay]', which has an error."

       

      DatesRankedByDay = 
ADDCOLUMNS(FILTER(All(Dim_Date),
 Dim_Date[BusinessDayFlag]= True), 
"Rank", RANKX( FILTER(All(Dim_Date)
, Dim_Date[BusinessDayFlag]= True)
, Dim_Date[FullDate],,ASC))
      • DaFloDo's avatar
        DaFloDo
        Resolver I
        7 years ago

        hi Anonymous,

         

        this will be a new calculated table. please see the attached file.

         

        https://www.dropbox.com/s/8sqtl08qu5onshn/after%2030%20days%20budiness%20days%20%28ranked%20dates%29.pbix?dl=1

         

         

        to clear things out: 
        first step is to create the new calculated table ranked by business days

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

        second step was adding a new calculated column to the peviously created table

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

         

         

        best regards 

         

        florian