Calculating the week number

[Anonymous]

Need help me, please!

I'm trying to create a calendar with the start of the week on Sunday and the first week of 2022 should start on January 02, 2022, respectively, January 1, 2022 should fall in the last week of 2021, but something goes wrong(

Calendar =

ADDCOLUMNS (

CALENDAR (

DATE ( 2018, 9, 1 ),

DATE ( YEAR(TODAY()) , 12, 31 ) ),

"WeekNumber", IF( YEAR ( [Date] ) = YEAR(TODAY()), WEEKNUM ( [Date], 1 ) - 1 , WEEKNUM ( [Date], 1 )),

"Year.WeekNumber", IF( YEAR ( [Date] ) = YEAR(TODAY()),

IF ( LEN( WEEKNUM([Date])-1) = 2, Year([Date])&"."& (WEEKNUM ( [Date], 1 ) - 1), Year([Date])&".0"&(WEEKNUM ( [Date], 1 ) - 1)),

IF ( LEN( WEEKNUM([Date])) = 2, Year([Date])&"."&WEEKNUM ( [Date], 1 ), Year([Date])&".0"&WEEKNUM ( [Date], 1 )))

[ValtteriN · Accepted solution]

Hi,

Something like this should work:

YearWeeknum = if(WEEKNUM('Calendar'[Date],1)-1=0,CONCATENATE('Calendar'[Year]-1 & "/",53),CONCATENATE('Calendar'[Year]& "/" ,WEEKNUM('Calendar'[Date])-1))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[ssz]

Hi ValtteriN,

great solution, thanks for sharing.
I suppose this new field has data type "text"?
I am wondering if there is a way to put this field (you explained) into a filter with Slicer settings "between".

Thanks in advance

ssz

[ValtteriN]

Hi,

Text type data can't be put to a between slicer. In that caseyou can format your year + weeeknum as a number e.g. 202301, 202302, 202303 and the put this into a slicer.

[ssz]

Thanks again, ValtteriN. This what I did. Unfortunately the slicer automatically filled all blank values between the min and max value.

This leads to [YearWeek] fields like 202260, 202270, 202299...
Is there anyway to only display existing values in slicer element.

Thanks

ssz

[Anonymous]

Hi @ValtteriN 

Could you help me figure out how to calulate week number based on a particular date column in my dataset? My week is also supposed to start from sunday unlike the conventional week. Thanks in advance!

[amitchandak]

@Anonymous , Sunday week start and end

Week Start date = [Date]+-1*WEEKDAY([Date],1)+1
Week End date = [Date]+ 7-1*WEEKDAY([Date],1)

Week num = weeknum([Date],1)

year Week num = year([Week Start date] ) *100 + weeknum([Date],1)