cancel
Showing results for
Did you mean:
Highlighted
Member

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

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", "")

4 REPLIES 4
Established Member

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", "")

Member

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!

Member

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!!

Established Member

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

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 288 members 3,285 guests
Recent signins: