Forum Discussion

AjaySingh's avatar
AjaySingh
New Member
4 years ago
Solved

Start counting week from a fixed date

Hi friends,

 

i want to visualize the perfomance of the new joinees on the basis of weeks, Week will start from the date of joining. for example if Steve joins on 2021-08-01 the i need the next seven days as week 1 and then Next 7 to 14 days as week 2 and so on.

 

I tried to use WEEKNUM but it is counting weeks from year start. 

Any help would be highly appreciated as i've just started in power bi.

 

 

 

Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    AjaySingh you should remove .DAY

     

    WEEKNUM( Table[CalledAt] )

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • AjaySingh's avatar
      AjaySingh
      New Member
      4 years ago

      Thanks for your suggestion however i am still getting the wrong week number.

       

      It should start with 1 instead of 2.

       




  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi AjaySingh 

    Weeknum() returns a fixed value, so try another way, 

    e.g. 

    Steve  join date:2021/9/1,
    week1:2021/9/1-2021/9/7,
    week2:2021/9/8-2021/9/14,
    week3:2021/9/15-2021/9/21,
    ...

    Weeknum = 
    var _joinDate=DATE(2021,9,1)
    var _duration=DATEDIFF(_joinDate,MIN('Table'[Column1]),DAY)
return INT(_duration/7)+1

    result

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.