cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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.

2 ACCEPTED SOLUTIONS
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.

Community Support

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!

2 REPLIES 2
Community Support

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!

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors