Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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(

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 )))
Need Help
  • ValtteriN's avatar
    ValtteriN
    4 years ago

    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!

6 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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!

    • ssz's avatar
      ssz
      Frequent Visitor
      3 years ago

      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's avatar
        ValtteriN
        Icon for Community Champion rankCommunity Champion
        3 years ago

        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.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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)