Forum Discussion
Get all values from week beginning with overlapping weeks from two months
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!
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.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 TeamIf 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!
2 Replies
- mickey64
Super User
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. 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 TeamIf 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!