Group Date Range from Week to Month

[ancook]

I'm wanting to group weeks together by month to get a MoM view. My data currently goes by week. How would I group the weeks together to go by 12 months rather than the weeks? If the weeks overlap into the next month (ie the highlighted below) I would consider that week February. 

[apathetichell]

datetimeformat(todate(regex_replace([Field1],"^(.*)( - .*)$","$1")),"%m") - or %b if you want 3 letter month. I'm getting a fake conversion error - but it works fine.

[jdminton]

If I'm understanding correctly, you want to break up the week that crosses over the month end to apply specific days into the month they belong. For that, you would need the data by day or allocate the data based on the number of days that week. Can you describe why you are trying to get the monthly data? That may help with what you can do in the event that the daily data is not available.

For many companies that work with weekly data, the months are viewed on a 5-4-4 or 4-4-5 week monthly schedule to avoid this issue. If you are comparing year over year, this may be sufficient. Can you share more about why you are needing the data?

[CoG]

Since, based off of your description, you consider crossing into the next month still (e.g. from highlighted data, week spanning February and March was counted as February), you can use the following Formula (I included 3 options):

Results:

[jdminton]

I somehow missed that last line. I would make it even simpler then by using Substring([Date Range],6,2) to get the month value. No need to do anything else unless you want to rename "02" to "February"