cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Usates Member
Member

Last Business Day

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.

 Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
KGrice Established Member
Established Member

Re: Last Business Day

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
KGrice Established Member
Established Member

Re: Last Business Day

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

 

 

Usates Member
Member

Re: Last Business Day

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!

Capture.PNG

Usates Member
Member

Re: Last Business Day

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 Established Member
Established Member

Re: Last Business Day

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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 288 members 3,285 guests
Please welcome our newest community members: