Last Business Day

[Anonymous]

Here is my current formula:

Today or Last Business Day = IF(date(year(DateDimension[DateKey]),month(DateDimension[DateKey]),day(DateDimension[DateKey]))=TODAY(),"Today")

This will display "Today" in the column, which I then can use as a "Page Filter".  I also need "Last Business Day", which will display "Last Business Day" on the day prior that is not a holiday or weekend.  Now, I have created a column, "WorkingDays" that puts a "0" on weekends and holidays and a "1" on all other days.  That formula is not pretty but it works: (try not to make too much fun of it)

WorkingDays = IF (OR (DateDimension[DayOfWeek] = 7, DateDimension[DayOfWeek] = 1), 0, if(DateDimension[DateKey] = value("01/01/2016"),0,if(DateDimension[DateKey] = value("03/25/2016"),0,if(DateDimension[DateKey] = value("05/30/2016"),0,if(DateDimension[DateKey] = value("07/04/2016"),0,if(DateDimension[DateKey] = value("09/05/2016"),0,if(DateDimension[DateKey] = value("11/24/2016"),0,if(DateDimension[DateKey] = value("11/25/2016"),0,if(DateDimension[DateKey] = value("12/23/2016"),0,if(DateDimension[DateKey] = value("12/26/2016"),0,if(DateDimension[DateKey] = value("12/30/2016"),0,1)))))))))))

Anyway, I need to expand on the "Today or Last Business Day" formula to put the word "Last Business Day" in the yellow highlighted area if a "1" is in the "WorkingDays" column.  Thanks for your help.

 

[KGrice · Accepted solution]

Is your DateDimension[DateKey] field a standard date? If so, why not start your Today or Last Business Day column as

=IF([DateKey]=TODAY(), "Today", "")

What you have now works, but if I understand the data, the formula above is easier to work with. From there, you can expand it to include your WorkingDays column. Your new Today or Last Business Day calculated column would then be

=IF([DateKey]=TODAY(), "Today", IF([DateKey]=CALCULATE(MAX([DateKey], FILTER(ALL(DateDimension), [WorkingDays]=1), FILTER(ALL(DateDimension), [DateKey]<TODAY())), "Last Business Day", "")

[Anonymous]

Found it

=IF([DateKey]=TODAY(), "Today", IF([DateKey]=CALCULATE(MAX([DateKey]), FILTER(ALL(DateDimension), [WorkingDays]=1), FILTER(ALL(DateDimension), [DateKey]<TODAY())), "Last Business Day", "")

Thank you so much for your help on this!!

[KGrice]

Sorry about the typo! I had some error trying to copy/paste and had to type it out manually. Glad you got it working!

[Anonymous]

Thanks for your help.  Yes, the [DateKey] is a standard date field.  I entered your suggestion and I received an error.  Could you review to see why please?  Thank you!