Week grouping not considering last week of the year

[Anonymous]

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
)

[amitchandak]

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

Connect on Linkedin

[Ashish_Mathur]

Hi,

What exact result are you expecting?

[Anonymous]

@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!

[v-yiruan-msft · Accepted solution]

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