cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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 8
DaFloDo
Resolver I
Resolver I

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

2018-10-10 12_41_28-Window.png

 

regards

 

Florian

Anonymous
Not applicable

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?

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
Not applicable

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

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

GarryFarrell
Advocate III
Advocate III

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
Not applicable

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

 

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!