For the challenge of week 17 of # WOW2020, one of the main requirements is to calculate the sales estimate at the end of the month, for which we need to calculate the days elapsed in the month and the total days of the month.

But knowing that the company doesn’t make sales on weekends, they ask us to calculate the difference between dates, considering only the days from Monday to Friday, instead of using the difference in calendar days.

Next, I share the steps I followed.

STEP 1. CALCULATE THE LAST DAY DATE OF SALES AND THE START AND END DATES OF THE MONTH.

Date MTD {FIXED : MAX([Date])} LOD calculation of the maximum date.
First Day DATETRUNC(“month”, [Date MTD]) The maximum date is truncated at the month level, to obtain the first day of the month.
Last Day DATEADD(“month”, 1, [First Day]) – 1 A month is added to the date of the first day of the month and 1 day is subtracted, to obtain the last date of the current month.

STEP 2. ADJUST THE START DATE CALCULATED IN STEP 1.

When the start date corresponds to Saturday or Sunday, it will be adjusted to consider that it starts on the following Monday, otherwise the original date will be left.

For which, simply, we must add two days, when the original date is Saturday and one day when the date is Sunday.

First Day Adjust [First Day] +
CASE DATENAME(“weekday”, [First Day])
WHEN “Saturday” THEN 2
WHEN “Sunday” THEN 1
ELSE 0
END

STEP 3. ADJUST THE END DATES CALCULATED IN STEP 1.

When the end date corresponds to Saturday or Sunday, they will be adjusted to consider that they end the previous Friday, otherwise, the original date will be left.

For which, simply, we must reduce one day, when the original date is Saturday, and two days when the date is Sunday.

Last Day Adjust [Last Day] +
CASE DATENAME(“weekday”, [Last Day])
WHEN “Saturday” THEN -1
WHEN “Sunday” THEN -2
ELSE 0
END
Date MTD Adjust [Date MTD] +
CASE DATENAME(“weekday”, [Date MTD])
WHEN “Saturday” THEN -1
WHEN “Sunday” THEN -2
ELSE 0
END

STEP 4. CALCULATE THE DIFFERENCE IN DAYS CONSIDERING ONLY FROM MONDAY TO FRIDAY.

To conclude the calculation we only need:

  • First, calculate the difference in calendar days between the adjusted end date and the adjusted start date and add one day.
  • Second, reduce two days for each week elapsed between the two dates, to exclude Saturday and Sunday.
Weekdays Month [Last Day Adjust] – [First Day Adjust] + 1 –
(DATEDIFF(“week”, [First Day Adjust], [Last Day Adjust]) * 2)
Weekdays MTD [Date MTD Adjust] – [First Day Adjust] + 1 –
(DATEDIFF(“week”, [First Day Adjust], [Date MTD Adjust]) * 2)

Now let’s continue with the rest of the challenge.

STEP 5. CALCULATE THE ESTIMATE OF SALES AT THE END OF THE MONTH.

Run Rate SUM([Sales]) * AVG([Weekdays Month]) / AVG([Weekdays MTD])

STEP 6. DETERMINE WHEN THE RUN RATE IS LESS THAN THE PLAN.

This field will help us define the color to be used in the bar corresponding to each Region.

Color [Run Rate] < AVG([Plan])

STEP 7. CREATE TWO COPIES OF THE RUN RATE FIELD, TO HELP US DEPLOY THE FIELD IN TWO COLORS.

This field will help us define the color to be used in the bar corresponding to each Region.

Run Rate (Black) IIF([Color], NULL, [Run Rate])
Run Rate (Red) IIF([Color], [Run Rate], NULL)

STEP 8. CREATE THE CHART.

Move to the shelves of:

  • Columns ⬅   AVG(0), Run Rate & AVG(Plan)
  • Rows ⬅   Region – Header is hidden

For the axis AVG(0), a text graphic will be used.

  • Detail ⬅   Date MTD
  • Tooltips ⬅   SUM(Sales), AVG(Plan), Run Rate (Black) & Run Rate (Red)

For the axis Run Rate, a bar graphic will be used.

  • Color ⬅   Color
  • Text ⬅   Run Rate

For the axis AVG(Plan), a Gantt chart will be used and it will be a double axis synchronized with the Run Rate axis.

Picture01

Ready!

The interactive version can be view here.

Picture00

An example with the calculation of weekdays for the 2020 calendar year can be view here.

Picture02

SUMMARY

To facilitate the calculation of weekdays between two dates, the first thing we must do is eliminate the effect of Saturdays and Sundays of the two dates involved.

How do we achieve this? Adjusting the dates involved.

First you have to identify when it’s the start date or the end date.

  • For the start date, when it corresponds to Saturday or Sunday, it will be adjusted to consider that it starts on the following Monday, otherwise the original date will be left.
  • For the end date, when it corresponds to Saturday or Sunday, it will be adjusted to consider that it ends the previous Friday, otherwise, the original date will be left.

With the adjusted dates, the calculation simply consists:

  • First calculate the difference in calendar days between the adjusted end date and the adjusted start date and add one day.
  • Second, reduce two days for each week elapsed between the two dates, to exclude Saturday and Sunday.

I love to find this type of calculation, where logic can be applied to make life easier for us.

Thank you very much and if you have any questions about the post, do not hesitate to contact me on Twitter (@rosariogaunag).