Hi ,

I have a requirement that i need to consider values only on first business day of selected month in prompt. That is first business day should not consider weekends and Holidays.

Say for ex : i am selecting july 2023 ,the it should consider July 3 since july 1 and 2 falls on weekend . If July 3 is holiday then it should consider july 4th.

I have calendar table which has weekdays and weekends and Holiday table which has list of holidays .

Using these 2 tables how can i calculate first business day of month. Please suggest

Thanks in advance.

------------------------------
Pavithra N
------------------------------

Hi Pavithra,

You can create a dataitem like this in your prompt query 

min([Date] for [YearMonth])

 and exclude weekends and holidays in your filter.

and set that as use value and still have the [YearMonth] as display in the prompt

Where [Date] and [YearMonth] comes from your calendar table,

Br Rene

#IBMChampion

------------------------------
René Kent Nielsen
Brand manager
CogniTech A/S
Herning
------------------------------

Rene gives a good answer if the calendar table includes all days.

But the opening post is a little unclear. Firstly it says "using these 2 tables", but only one table is mentioned. It also is unclear if the table with holidays and weekends includes all days.

------------------------------
Marc Reed
------------------------------

Hi Pavithra,

I tried this using joins and it seems to be working.

1. Started of by having an Excel file with one sheet for Date and whether it is a weekday or weekend. You could replicate the column for weekday/weekend using a query calculation with the _day_of_week function.
   

   
2. Then I joined the two tables into a third table.
   • Filtered to show only null, as this way only dates that are not holidays will be populated.
   • Then I added a calculated field for month, to match with the month parameter coming from the query, see below query info
   • WITH 
     DAYS_WITH_HOLIDAY AS 
         (
         SELECT
             month(DAYS_0.Date_) AS C_Date, 
             DAYS_0.Date_ AS Date_, 
             DAYS_0.DayOfWeek AS DayOfWeek, 
             HOLIDAY0.Holiday AS Holiday
         FROM
             2765571786...DAYS_ DAYS_0
                 LEFT OUTER JOIN 1275846912...HOLIDAY HOLIDAY0
                 ON DAYS_0.Date_ = HOLIDAY0.Date_ 
         WHERE 
             DAYS_0.DayOfWeek IN ( 
                 'WEEKDAY' ) AND
             HOLIDAY0.Holiday IS NULL
         )
     SELECT
         DAYS_WITH_HOLIDAY.C_Date AS C0, 
         DAYS_WITH_HOLIDAY.Date_ AS C1, 
         DAYS_WITH_HOLIDAY.DayOfWeek AS C2, 
         DAYS_WITH_HOLIDAY.Holiday AS C3
     FROM
         DAYS_WITH_HOLIDAY
     
3. Now in the report I made a query with the Date and month from the DAYS_WITH_HOLIDAY table. Then added a filter for month and finally change the detail aggregation from NONE to MINIMUM
4. Now you have a table that shows the first business day of the month coming from the prompt.

Do excuse me as, I am not too good with data modules to see if this could be done within a Data Module, instead of doing it at the report.

------------------------------
Manu G Panicker
------------------------------

Certainly, you can address this requirement by implementing a logic that skips weekends and holidays when selecting values on the first business day of a chosen month. To achieve this:

1. Identify the selected month.
2. Determine the first day of the month.
3. Check if the first day is a weekend (Saturday or Sunday), and if so, advance to the next business day.
4. Check if the adjusted first day is a holiday; if yes, skip to the next business day.
5. Use the adjusted first business day to consider values as required.

By considering weekends and holidays, you ensure accurate data selection on the first business day of the chosen month.

------------------------------
shoaib bhatti
------------------------------