topic Re: Calculating the week number in DAX Commands and Tips

Calculating the week number

Anonymous — Tue, 11 Jan 2022 12:27:24 GMT

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 )))

Re: Calculating the week number

amitchandak — Tue, 11 Jan 2022 12:46:24 GMT

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)

Re: Calculating the week number

ValtteriN — Tue, 11 Jan 2022 12:45:02 GMT

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!

Re: Calculating the week number

Anonymous — Wed, 30 Mar 2022 14:30:45 GMT

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!

Re: Calculating the week number

ssz — Fri, 23 Dec 2022 10:21:00 GMT

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

Re: Calculating the week number

ValtteriN — Fri, 23 Dec 2022 11:55:34 GMT

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.

Re: Calculating the week number

ssz — Fri, 23 Dec 2022 12:58:34 GMT

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