DAX Relative Business Day Offset Formula help

[davidqsuires]

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. 

[davidqsuires · Accepted solution]

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]

[ankitpatira]

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

[davidqsuires]

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.