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

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.

  • ssz's avatar
    ssz
    Frequent Visitor
    3 years ago

    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