Use previous date if selected date falls on specified holiday

[iammrishoabeid]

Hi Power BI Community,

I have a data set with a check date when users must complete a certain milestone. Unfortunately, the original dataset does not consider weekends, UK public holidays, or specific closure days for the business. I need to check if a check date falls on any of these dates. If so, I want to select the last working day before the check date.

So for instance, if a check date falls on 28 December which is a business closure day between 25 December and 01 January, then I want to select 27 December instead.

How can I achieve this in Power BI? I have attached a screenshot of a visual which shows the milestones in question and the dates they are due for checks.

Thanks in advance for your support.

 

[tharunkumarRTK · Accepted solution]

@iammrishoabeid 

I think there are many ways to solve this, create a list of holidays and assign it to a variable in dax and check if the selected value is in that list.

for example:

BusinessDayCheck = 
Var __holidays = { dt"27-12-2023", dt"20-12-2023"  }
Var __selectedDate = selectedvalue(dateTable[Date])
Var result = if(__selectedDate in __holidays, __selectedDate - 1, __selectedDate)
return result

if the no of the holidays are large in number then you use NETWORKDAYS Dax function and check if the selected value falls under the business days or not.

https://dax.guide/networkdays/

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[tharunkumarRTK · Accepted solution]

@iammrishoabeid 

I think there are many ways to solve this, create a list of holidays and assign it to a variable in dax and check if the selected value is in that list.

for example:

BusinessDayCheck = 
Var __holidays = { dt"27-12-2023", dt"20-12-2023"  }
Var __selectedDate = selectedvalue(dateTable[Date])
Var result = if(__selectedDate in __holidays, __selectedDate - 1, __selectedDate)
return result

if the no of the holidays are large in number then you use NETWORKDAYS Dax function and check if the selected value falls under the business days or not.

https://dax.guide/networkdays/

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[iammrishoabeid]

Hi Tharun,

Thanks for getting back to me so promptly and for sharing your suggestion.

Unfortunately, I tried it but the values do not show up in the relevant column. However, when I add a list in the "__selectedValue" variable, I get the dates. Is there anything I may be doing wrong?

I have attached an image showing the code I have used as per your example, and the (Blank) results I am receiving in the relevant column.

 

[tharunkumarRTK · Accepted solution]

@iammrishoabeid 

You are trying to create a column, so place the selectedvalue function with the actual date column name. That should be enough.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[iammrishoabeid]

Thanks for the advice Tharun. You suggestion appears to have worked so far. However, I am now faced with another issue which I hope you may be able to advice me on as well.

The issue is that at the moment, the code only performs a check once. Meaning, if the new date still falls in the __holidays list, it will not be rechecked. I have tested this with 27 DEC 2023 which is in the list together with 26 and 25 DEC 2023. I expected it to continue checking until it sets a new date of 24 DEC 2023, but it has not.

How can I potentially get it to continue checking until the condition is false before proceeding to the next date in the table?

I have attached a screenshot showing the current progress and the potential issue.