Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
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
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
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.
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
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
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