Calculate day on Day changes excluding holiday and weekends

[fernandoz]

I am using a time intelligence function “Previousday” also tried with “Dateadd” and I cannot return the previous day when there is a holiday or a weekend in my fact table. The date table has every date and the filter with my fact table linking to the as-of-date in my model to fact table if I choose the date from the date table from your resources ( Dates Table) because includes every day including holidays and weekends. I just took the enterprise DNA course with Time intelligence DAX trying to find the solution but the example you have Month over Month change with custom calendar using variables to solve seems close but I got stuck . Examples below
What would be the DAX logic I need to use to solve this? Any advise

[Melissa]

Hi @fernandoz,

Welcome to the forum!

This will calculate the previous working day:

Previous workingday = 
CALCULATE( MAX( Dates[Date] ),
    FILTER( ALL( Dates ),
        Dates[IsWorkingDay] = TRUE() &&
        Dates[Date] < MAX( Dates[Date] )
    )
) 

If you don’t have an IsWorkingDay column in your Dates table enter this formula in a calculated column:
NOT WEEKDAY( Date[Date] ) IN { 1,7 }

You can do the same for holidays, add a boolean filter to your Dates table so you can identify them and exclude them inside the Filter logic.

Once you have the [Previous workingday], you can calculate the [Market Value Units] like so:

Previous workingday Value = 
CALCULATE( [Market Value Units],
    FILTER( ALL( Dates ),
        Dates[Date] = [Previous workingday] )
    )
) 

I hope this is helpful.

Here’s an article you might find intresting.

Reference Date Table in DAX and Power BI - SQLBI

[fernandoz]

Hi @Melissa ,
Thank you for your prompt response. I follow your guidance but at the end the Previous workingdate Value is returning blank. See the print screens for the steps I did. Any clues why?

!
PWD 4|690x304

[fernandoz]

[Melissa]

@fernandoz, can you provide a sample? I know we can resolve this.

In the mean time here’s my test file, containing a working solution, mayby you find that helpful.
Changed one pattern slightly:

Sales previous day = 
VAR PrevDay = [Previous workingday]
RETURN

CALCULATE( [Market Value Units],
    FILTER( ALL( Dates ),
        Dates[Date] = PrevDay
    )
)

.
File: eDNA - Calculate day on Day changes excluding holiday and weekends.pbix (306.0 KB)

[fernandoz]

Hi @Melissa
Thank you so much for your help. It work after I made the changes with the VAR. Now I need to solve the Holiday problem now the weekend is solve. As you can see in the picture 01/01/ 2020 is a Holiday and 1/20/2020 is a holiday in USA …President’s Day. How do I incorporate a Holiday Calendar ( like USA) in my date table so I can Boolean in the [Previous workingday] logic that only factor weekends

[Melissa]

Well first add a supporting table that contains all the holidays you want to exclude, something like below. It doesn’t need to have any relationship AND you can hide the entire table from report view.

I named the table tHolidays, next go to your Dates table and add a calculated column IsHoliday:
Dates[Date] IN VALUES(tHolidays[Date])

.
Now all we need to do is add this new Date table column to the filter condition.

Previous workingday wo holidays = 
CALCULATE( MAX( Dates[Date] ),
    FILTER( ALL( Dates ),
        Dates[IsWorkingDay] = TRUE() &&
        Dates[IsHoliday] = FALSE() &&
        Dates[Date] < MAX( Dates[Date] )
    )
) 

I hope this is helpful.

[fernandoz]

Thank you vevery much @Melissa . It worked. Regards, Fernando

[Melissa]

@fernandoz that’s great

One question. Could you please mark the correct answer as solution? You can do so by going to the three dots uncheck and check the relevant post. That’ll help other members find the solution more quickly in future.

Thank you.