Forum Discussion

davidqsuires's avatar
davidqsuires
Frequent Visitor
9 years ago
Solved

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 ...
Need Help
  • davidqsuires's avatar
    davidqsuires
    9 years ago

    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's avatar
ankitpatira
Community Champion
9 years ago

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's avatar
davidqsuires
Frequent Visitor
9 years ago

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.