Forum Discussion

davidqsuires's avatar
davidqsuires
Frequent Visitor
10 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
    10 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]
davidqsuires's avatar
davidqsuires
Frequent Visitor
10 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]