cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

Week grouping not considering last week of the year

Hi all,

I have the calculated column below to group my week as per the week end date. This was a suggestion from a community member and it worked very well except for the last week of the year which has december and then goes into January. The formula then breaks that into 2 different weeks. Please see below. Does anyone know a way to group that last week together as well?

```Column 2 =
VAR __yearWeek = FORMAT( 'Calendar'[Date], "YYYYWW" )
RETURN
CALCULATE(
MAX( 'Calendar'[Date] ),
ALL( 'Calendar' ),
FORMAT( 'Calendar'[Date], "YYYYWW" ) = __yearWeek
)```

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

Please try to create the calculated column getNextSat :

getNextSat = 'Calendar'[Date]
+ SWITCH (
WEEKDAY ( 'Calendar'[Date], 1 ),
1, 6,
2, 5,
3, 4,
4, 3,
5, 2,
6, 1,
7, 0
)
You can get the sample pbix file from this link.

Best Regards
Rena
Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User

It is better that you create a week start or weekend date and then use this

``````Week End date = DATEADD('Compare Date'[Compare Date],7-1*WEEKDAY('Compare Date'[Compare Date]),DAY)
Week Start date = DATEADD('Compare Date'[Compare Date],-1*WEEKDAY('Compare Date'[Compare Date])+1,DAY)``````

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Super User

Hi,

What exact result are you expecting?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

@Ashish_Mathur Expected result is to group 7 days from Sunday to Saturday and show the last day of the week, for example for the week from 01/05/2020 to 01/11/2020 I want to see 01/11/2020  , for the week from 01/12/2020 to 01/18/2020 I want to see 01/18/2020 and so on.

Thanks!

Community Support

Hi @Anonymous ,

Please try to create the calculated column getNextSat :

getNextSat = 'Calendar'[Date]
+ SWITCH (
WEEKDAY ( 'Calendar'[Date], 1 ),
1, 6,
2, 5,
3, 4,
4, 3,
5, 2,
6, 1,
7, 0
)
You can get the sample pbix file from this link.

Best Regards
Rena
Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors