Forum Discussion
DAX Relative Business Day Offset Formula help
Hi Everyone,
I'm new to DAX and having trouble trying to create a calculated field in my SSAS tabular model. I have data that looks like this:
| Day | Date | Business Day Flag | Max Inv Date | Relative Offset Calendar Days | Relative Offset Business Days (desired) |
| Thursday | 9/8/2016 | 1 | 9/12/2016 | -4 | -2 |
| Friday | 9/9/2016 | 1 | 9/12/2016 | -3 | -1 |
| Saturday | 9/10/2016 | 0 | 9/12/2016 | -2 | 0 |
| Sunday | 9/11/2016 | 0 | 9/12/2016 | -1 | 0 |
| Monday | 9/12/2016 | 1 | 9/12/2016 | 0 | 0 |
| Tuesday | 9/13/2016 | 1 | 9/12/2016 | 1 | 1 |
The calulated field i'm trying to create is in BOLD on the far right.
You can see i am trying to create a dynamic relative off set, but not include weekends like the "Relative Offset Calendar Days" column is doing.
So basically "Today" is 9/12/16 and i want to be able to select 0,-1,-2 (the last 3 business days) in this new column for this scenario, and it brings back Thursday 9/8 & Friday 9/9 & Monday 9/12. Some people might want the last 4, 5, 10, etc. business days.
The next day will be 9/13/16 Max Invoice Date, and those relative offsets will be re-built appropriately.
I think it's involves Calculate and EARLIER functions but i'm not even sure of that.
Thanks for any help anyone can provide.
If it helps anyone else, a co-worker of mine was able to get it to work doing the following. It's broken out into a few steps to help work through it:
Calculated field to make the business day flag the opposite:
NonBusinessDay=IF('Invoice Date'[Business Day Flag]=FALSE(),1,0)Calculated field to sum the non-business days relative to a date. There are existing blogs on creating a regular Relative Date Offset: =
CALCULATE ( SUM ( [NonBusinessDay] ), ALL ( 'Invoice Date' ), FILTER ( ALL ( 'Invoice Date' ), 'Invoice Date'[Relative Date Offset] >= EARLIER ( [Relative Date Offset] ) && 'Invoice Date'[Relative Date Offset] < 0 ) )
Final field:
=[Relative Date Offset]+[RelativeNonBusinessDayCount]
3 Replies
- ankitpatira
Community Champion
davidqsuires Calculated column as below will do the trick for you. Basically you're determining if difference between date is greater than 1 week then number of weeks * 5 (business days), if difference is 1 week or 0 days then 0.
Column = IF( DATEDIFF( sam[Date],sam[Max Inv Date], WEEK) > 1, DATEDIFF( sam[Date], sam[Max Inv Date], WEEK) * 5,
IF( DATEDIFF( sam[Date],sam[Max Inv Date], DAY) = 1, 0,
IF( DATEDIFF( sam[Date],sam[Max Inv Date], DAY) = 0 , 0, DATEDIFF( sam[Date], sam[Max Inv Date], DAY) - 2
)
)
)Thank you ankitpatira for the response. I should have mentioned, there could be scenarios where the business day flag is 0 for mid week holidays, so I would think the formula would have to refernce the business day flag. e.g. July 4th was on a Monday this year, so that week would have 2 weekend days, and the Monday July 4th holiday as non-business days.
If it helps anyone else, a co-worker of mine was able to get it to work doing the following. It's broken out into a few steps to help work through it:
Calculated field to make the business day flag the opposite:
NonBusinessDay=IF('Invoice Date'[Business Day Flag]=FALSE(),1,0)Calculated field to sum the non-business days relative to a date. There are existing blogs on creating a regular Relative Date Offset: =
CALCULATE ( SUM ( [NonBusinessDay] ), ALL ( 'Invoice Date' ), FILTER ( ALL ( 'Invoice Date' ), 'Invoice Date'[Relative Date Offset] >= EARLIER ( [Relative Date Offset] ) && 'Invoice Date'[Relative Date Offset] < 0 ) )
Final field:
=[Relative Date Offset]+[RelativeNonBusinessDayCount]
