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