Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
KGrice
Memorable Member
Memorable 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", "")

 

 

View solution in original post

4 REPLIES 4
KGrice
Memorable Member
Memorable 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", "")

 

 

Anonymous
Not applicable

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

 

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.