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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
AjaySingh
New Member

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.

 

AjaySingh_0-1633402049954.png

 

 

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @AjaySingh 

vxiaotang_0-1633572800606.png

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

vxiaotang_1-1633573927409.png

 

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.

View solution in original post

3 REPLIES 3
v-xiaotang
Community Support
Community Support

Hi @AjaySingh 

vxiaotang_0-1633572800606.png

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

vxiaotang_1-1633573927409.png

 

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.

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

 

It should start with 1 instead of 2.

AjaySingh_0-1633412148203.png

 




Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.