Last Business Day that incorporates Holidays

[Jos1986]

Hello. We currently use the below formula to calculate last day sales. As you can see it takes into account weekends and skips those days. However, we want to also exclude our company holidays which are known dates. Is there any way I can bring that into this DAX at all or do I have to start with an entirely new measure?

Last Day Sales (USD) =

VAR _Today =

    TODAY ()

VAR _latestDate = _Today - 1

VAR _DD =

    SWITCH (

        WEEKDAY ( _Today ),

        2, _Today - 3,

        1, _Today - 2,

        7, _Today - 1,

        _latestDate

    )

RETURN

    CALCULATE ('Sales'[All Sales (USD)], 'Date'[Date] = _DD)

[vicky_ · Accepted solution]

If you have known holidays, put those in a table and use the NETWORKDAYS function. so something like

Last Day Sales (USD) = CALCULATE(MAX('date'[Date]), NETWORKDAYS('date'[Date],  TODAY(), 1 , holiday) = 2), 

where holiday is the name of the table containing your company holidays.