The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have a date table I created beginning with a specific date - 5/18/2018. I have it set up to populate a date entry for every date between that date and today so overtime dates will continue to get added (I used this DAX code: CALENDAR("5,18,2018",TODAY()) ) I now need to keep track of the number of weeks that have passed from 5/18/2018 to today each week and I need to be able to assign which week date falls under. This is to track how long a version of software has been in GR. Listed below is what I would like to achieve. I tried using WEEKNUM but that starts as of 1/1/2018. Does anyone know how to do this?
Date | Weeks since GR |
5/18/2018 | 1 |
5/19/2018 | 1 |
5/20/2018 | 1 |
5/21/2018 | 1 |
5/22/2018 | 1 |
5/23/2018 | 1 |
5/24/2018 | 1 |
5/25/2018 | 2 |
5/26/2018 | 2 |
5/27/2018 | 2 |
5/28/2018 | 2 |
5/29/2018 | 2 |
5/30/2018 | 2 |
5/31/2018 | 2 |
You need to add a column which uses DATEDIFF function.
=DATEDIFF(date1, date2, weeks)
Basically provides the difference between the two dates in weeks.
Sorry - I forgot mention that I tried that and this is what I get for that what is shown below. If I reverse it, I get negative values.
I see that it's calculating correctly. Or did you want something else? Can you provide what numbers you are expecting?
What I was looking to achieve is in my original post (scroll up). I actually just figured it out (see below) - I should have been using the start date instead of today().
Thanks!
@krfaughnan Please try with this...
Test08 = ADDCOLUMNS( CALENDAR("2018-05-18",TODAY()), "WeekNo",IF(WEEKDAY([Date],2)>=5, WEEKNUM([Date],2)+1,WEEKNUM([Date],2))-20 )
Proud to be a PBI Community Champion
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
83 | |
64 | |
58 |
User | Count |
---|---|
248 | |
123 | |
112 | |
79 | |
78 |