cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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", "")

 

 

View solution in original post

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

 

 

View solution in original post

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
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)