Week Start/End Date that only uses current month

[crobson29]

Hello everybody,

I have an interesting problem, I am building a weekly averages matrix and am trying to get the weeks labeled correctly.  Currently, I have a column in the date table that shows the Week Starting date, a separate one showing week ending, and one that is concatenating them into the format "Start Date - End Date".  In the middle of the month, this is perfect.  The issue that I am having is that the beginning of the month usually isn't in the month is question, and it makes the visual a bit confusing.  For example, the first week in august starts on 7/29, so the label is "7/29/2024 - 8/3/2024".  However, the average is only being calculated on the days in August, so this could cause confusion.  The label that I would like to have is "8/1/2024 - 8/3/2024".  Is this possible?

[darkniqht · Accepted solution]

Instead of using the start of the week, you can check if the week starts in a prior month. If it does, adjust your label to display from the first day of the current month to the week end date. This way, for the first week of August, the label would show as "8/1/2024 - 8/3/2024" instead of "7/29/2024 - 8/3/2024.

[crobson29]

This is what worked, and I did the same thing with the end of the month using ENDOFMONTH instead of 1 so that it takes into account leap years and such.  

[lbendlin]

use something like MAX([Start Date],STARTOFMONTH([End Date])).  The usual statement applies - months and weeks are incompatible.