Calculate the latest previous working day based on Holiday column

[toanpham0511]

Hi everyone, I am trying to use DAX to calculate the previous working day base on a date column, I think I will use this dax in a virtual table in PowerBI, I expect the dax function to return the previous working day if the evaluated date is a holiday. For example, suppose that 1 Jan 2025, 2 Jan 2025, 3 Jan 2025 are holidays, so I expect the dax to return 31/12/2024 for all 3 rows of 1st Jan, 2nd jan, 3rd Jan as the latest previous working date of these 3 dates are 31/12/2025. Thank you so much for your help. This PowerBI community is really helpful to me since I started my data journey. Really appreciate it.

[ryan_mayu · Accepted solution]

@toanpham0511 

you can try to create a calculated column

Column = maxx(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])&&'Table'[Holiday]<>"Y"),'Table'[Date])

[TomMartens]

Hey @toanpham0511 ,

Here you will see a measure that derives the previous working day considering weekends and holidays.
It works like this:
Get the current day, filter all days smaller than the current day that are not weekends and not holidays, and then get the max date.

previous workingday = 
var currentDay = CALCULATE( MAX( 'DimDate'[Datekey] ) )
return
MAXX(
    FILTER(
        ALLSELECTED( 'DimDate' ),  'DimDate'[Datekey] < currentDay && DimDate[IsWorkDay] = "Workday" && 'DimDate'[IsHoliday] = 0
    ),
    'DimDate'[Datekey]
)

A small table visual:

Hopefully, this will provide you with ideas on how to tackle your challenge.

Regards,

Tom

[toanpham0511]

Thank you for your help, I will try your solution, your support is highly appreciated