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, 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