Filtering on Last Business day of Month (Community)

View the original community article here

Last tested: Feb 2, 2021
 

dimension: returned_last_business_day {

type: yesno

sql: CASE WHEN EXTRACT( day from DATEADD(day,2,${returned_date_raw}) ) = 1 AND ${returned_day_of_week} = "Friday"

THEN true

WHEN EXTRACT( day from DATEADD(day,3,${returned_date_raw}) ) = 1 AND ${returned_day_of_week} = "Friday" then true

WHEN EXTRACT( day from DATEADD(day,1,${returned_date_raw}) ) = 1 then true else false END;;

Basically, what this does is check to see if the second to last day of the month is Friday, if so that means that the last day of the month for that month was Saturday. If the third to last day of the month is Friday, it means that the last day of that month was a Sunday.