CASE
WHEN DAYOFWEEK("YOURDATEFIELD") BETWEEN 2 AND 6
AND "ORDER_DATE_NEW" = CASE
DAYOFWEEK(DATE_TRUNC('MONTH', "YOURDATEFIELD"))
WHEN 1 THEN DATEADD('day', 1, DATE_TRUNC('MONTH', "YOURDATEFIELD")) -- If 1st is Sunday
WHEN 7 THEN DATEADD('day', 2, DATE_TRUNC('MONTH', "YOURDATEFIELD")) -- If 1st is Saturday
ELSE DATE_TRUNC('MONTH', "YOURDATEFIELD") -- If 1st is weekday
END
THEN 'Y'
WHEN DAYOFWEEK("YOURDATEFIELD") BETWEEN 2 AND 6
THEN 'N'
END
When you replace YOURDATEFIELD -- please make sure you are using the table's Source name and not the Field name
Understanding the above code:
It only looks at weekdays (Monday through Friday) and ignores weekends completely.
It figures out the first weekday of each month - if the 1st falls on a weekend, it moves to the following Monday.
It returns 'Y' if the date is that first weekday of the month, otherwise it returns 'N' for all other weekdays.
The result is a flag that marks only the first business day of each month - useful for identifying month-start dates in business reporting.