Afternoon experts!
I need to create a target, that is based on the number of weeks in a given month, with a target of 1 per week, so that I can achieve conditional formatting on my table.
Example: Feb '22 has 4 weeks, so a target of 4, whereas March '22 has 5 weeks, so needs a target of 5.
Any ideas?
Let’s start by defining what a ‘week’ is. Number of Sundays, Monday’s, Friday’s…
Extending what Bo_Bozo has said, this may even vary year to year, based on financial calendar, leap years, etc.
Once that is determined, I would look at a solution in the universe with a date dimension / calendar table to support your reporting requirements. While this assumes that you have access to the underlying database to create (or simply enhance) a calendar table, it will provide you with a far more powerful and robust solution that will perform better than putting the heavy lifting into the report (which will then need to be replicated in multiple reports).
This is the sort of table I’m referring to: Create an Extended Date Dimension for a SQL Server Data Warehouse