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
JBST
Frequent Visitor

Determine last two weekday business days of month

Good morning

 

Currently I have a date table and am needing an additional column in my table (or combination of calculated columns and measures) to classify if a day fits the business definition of the last two days of the month so we can report on invoicing raised on these days

 

In this instance I need to be able to tag a day to be included based on the following

 

  • Determine last 2 weekdays of a calendar month – these will be referred to as the last 2 business days
  • However if one or both of the final two calendar days of the month fall on the weekend they need to be tagged as the prior Friday (thus being included in the last two weekdays of the month)
  • This could result in some instances requiring the last 4 calendar days of the month (ie Thurs/Fri/Sat/Sun – or Fri/Sat/Sun/Mon)
  • If last two days are origianlly weekdays they can remain as they appear in the date table

 

The date table has defined what is a weekday/weekend and every date has its own individual key and date ID – started with the theory of determining 1 (Sat) or 2 (Sun) days back to reclassify each weekend date as the prior Friday giving it the date of the Friday but am hitting a hurdle when I attempt to then tag the final two business days of the month

 

Any assistance would be greatly appreciated

 

Thank you

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @JBST

 

There are multiple ways of doing this using Power Query or DAX.

 

Below is a suggestion using a DAX calculated column added to your existing Date table. This just relies on there being a 'Date'[Date] column, but you could replace parts of this code with references to existing columns.

 

From your description I think there are three cases to handle:

  • Last day of the month is Monday or Sunday => Include 4 days (Fri/Sat/Sun/Mon or Thu/Fri/Sat/Sun)
  • Last day of the month is Saturday => Include 3 days (Thu/Fri/Sat)
  • Otherwise => Include 2 days

Hopefully that's right otherwise you can modify the code to suit.

 

Last 2 days flag = 
VAR CurrentDayOfMonth =
    DAY ( 'Date'[Date] )
VAR LastDateOfMonth =
    EOMONTH ( 'Date'[Date], 0 )
VAR LastDayOfMonth =
    DAY ( LastDateOfMonth )
VAR WeekdayOfLastDayOfMonth =
    WEEKDAY ( LastDateOfMonth, 3 ) // Monday=0, Sunday=6
VAR NumberOfDaysToInclude =
    SWITCH (
        TRUE (),
        WeekdayOfLastDayOfMonth IN {0, 6}, 4, // 4 days if month ends on Mon or Sun
        WeekdayOfLastDayOfMonth = 5, 3, // 3 days if month ends on Sat
        2 // 2 days otherwise
     )
VAR Include =
    IF ( LastDayOfMonth - CurrentDayOfMonth + 1 <= NumberOfDaysToInclude, 1, 0 )
RETURN
    Include

This returns a 0/1 flag but you could change it to true/false or text or whatever you want.

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @JBST,

 

Have you solved your problem by the formula provided by @OwenAuger

 

If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you need additional help please share some data sample and expected output, so that we can help further investigate on it?

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
OwenAuger
Super User
Super User

Hi @JBST

 

There are multiple ways of doing this using Power Query or DAX.

 

Below is a suggestion using a DAX calculated column added to your existing Date table. This just relies on there being a 'Date'[Date] column, but you could replace parts of this code with references to existing columns.

 

From your description I think there are three cases to handle:

  • Last day of the month is Monday or Sunday => Include 4 days (Fri/Sat/Sun/Mon or Thu/Fri/Sat/Sun)
  • Last day of the month is Saturday => Include 3 days (Thu/Fri/Sat)
  • Otherwise => Include 2 days

Hopefully that's right otherwise you can modify the code to suit.

 

Last 2 days flag = 
VAR CurrentDayOfMonth =
    DAY ( 'Date'[Date] )
VAR LastDateOfMonth =
    EOMONTH ( 'Date'[Date], 0 )
VAR LastDayOfMonth =
    DAY ( LastDateOfMonth )
VAR WeekdayOfLastDayOfMonth =
    WEEKDAY ( LastDateOfMonth, 3 ) // Monday=0, Sunday=6
VAR NumberOfDaysToInclude =
    SWITCH (
        TRUE (),
        WeekdayOfLastDayOfMonth IN {0, 6}, 4, // 4 days if month ends on Mon or Sun
        WeekdayOfLastDayOfMonth = 5, 3, // 3 days if month ends on Sat
        2 // 2 days otherwise
     )
VAR Include =
    IF ( LastDayOfMonth - CurrentDayOfMonth + 1 <= NumberOfDaysToInclude, 1, 0 )
RETURN
    Include

This returns a 0/1 flag but you could change it to true/false or text or whatever you want.

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi Owen

 

Thank you so much for your guidance as this solution works perfectly in all given scenarios within my date table. 

 

Regards

 

Jaime

 

 

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.