Get all values from week beginning with overlapping weeks from two months

[socal225]

I have a calendart table related to a sales table by date. I have created a year, month, week num,  week of month, and week beggining.

I want to filter the page by year and month, and then add the week of month field as columns. However, if a week overlaps, say week begins 01/28/24 and ends 02/03/24, i want to get the values from 01/28 onwards and throw it under the new week number 1 for February. Below is an example of where they overlap, and how I would like for it to look.

Thank you, in advance!

[mickey64 · Accepted solution]

Step 0: I use your data below. (Date:yyyy/mm/dd)

Step 1: I make a measure.

In this measure, I compare the month number of the first day of the week to the month number of four days after the first day of the week.

the first day of the week --> Monday

four days after the first day of the week --> Friday

M_W Num Cal = IF(MONTH(MAX('DATA'[Week Beginning]))<>MONTH(DATE(YEAR(MAX('DATA'[Week Beginning])),MONTH(MAX('DATA'[Week Beginning])),4+DAY(MAX('DATA'[Week Beginning])))),1, 1+MAX('DATA'[Week Number])-WEEKNUM(DATE(YEAR(MAX('DATA'[Week Beginning])),MONTH(MAX('DATA'[Week Beginning])),1)))

Step 2: I make a table.

[v-linhuizh-msft · Accepted solution]

Hi @socal225 ,

Your solution is great, @mickey64 . Here I have another idea in mind, and I would like to share it for reference.

1.Create simple data:

2.Create new measures:

WENK END = SELECTEDVALUE('Table'[Week Beginning])+6


Week number of month = 
VAR _month = MONTH(SELECTEDVALUE('Table'[Week Beginning])+6)
RETURN
RANKX(FILTER(ALL('Table'), MONTH('Table'[WENK END]) = _month), [WENK END], , ASC, Dense)

3.The result is as follows:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[mickey64 · Accepted solution]

Step 0: I use your data below. (Date:yyyy/mm/dd)

Step 1: I make a measure.

In this measure, I compare the month number of the first day of the week to the month number of four days after the first day of the week.

the first day of the week --> Monday

four days after the first day of the week --> Friday

M_W Num Cal = IF(MONTH(MAX('DATA'[Week Beginning]))<>MONTH(DATE(YEAR(MAX('DATA'[Week Beginning])),MONTH(MAX('DATA'[Week Beginning])),4+DAY(MAX('DATA'[Week Beginning])))),1, 1+MAX('DATA'[Week Number])-WEEKNUM(DATE(YEAR(MAX('DATA'[Week Beginning])),MONTH(MAX('DATA'[Week Beginning])),1)))

Step 2: I make a table.