Bringing Last Business Day Data

[Kristie_Pires]

Hello all,

The data in our system is always one business day behind. To bring that data in, I have a formula to bring yesterday's data however it doesn't work for holidays. This is the formula I am currently using: 

Yesterday formula: If datetimeformat(DateTimeToday(),'%a')='Mon' then DateTimeFormat(DateTimeAdd(DateTimeToday(),-3,"days"), "%Y-%m-%d") else DateTimeFormat(DateTimeAdd(DateTimeToday(),-1,"days"), "%Y-%m-%d") endif

Last Bussiness Day Formula: IF [Yesterday]=[Processed Time] THEN "Last BD" ELSE "Exclude" ENDIF

After using the two formulas, I filter only "Last Business Day" to get yesterday data. However, if it is a holiday, I have to manually bring the data. Is there any way I can dynamically bring the data? 

[marcusblackhill]

Hi @Kristie_Pires,

That is always tricky to solve. The most common solution is as you build a dataset in a text input with all holidays first.

To keep your current logic, you could add a find/replace or join tool after your formula to bring a flag in case the day "Yesterday" date is a holiday and add a new formula tool to adjust the date in case it is a holiday.