Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculating the week number

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(

mrai_0-1641903837435.png

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 )))
1 ACCEPTED SOLUTION
ValtteriN
Super User
Super User

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

ValtteriN_0-1641905073126.png

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!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
ValtteriN
Super User
Super User

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

ValtteriN_0-1641905073126.png

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!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ssz
Frequent Visitor

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

ssz_0-1671790651674.png

 

Thanks in advance

ssz

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ssz
Frequent Visitor

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
Not applicable

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
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.