Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a data series starting on the week of 7/29/2019. I need a formula that flag with 1 to the first week (7/29/2019), 2 to the second week (8/5/2019) and so on.
How can I tell DAX to start on the week of 7/29/2019 and incrementally add 1 to the following weeks?
Thanks!
Ezequiel
Solved! Go to Solution.
Hello @ezequiel
Give this a try.
Column = DATEDIFF ( DATE ( 2019 , 7 , 28 ), 'YourTable'[Date] , WEEK ) +1
The +1 is on the end so the first 7 days don't come back as 0
Hello @ezequiel
Give this a try.
Column = DATEDIFF ( DATE ( 2019 , 7 , 28 ), 'YourTable'[Date] , WEEK ) +1
The +1 is on the end so the first 7 days don't come back as 0
Hi @jdbuchanan71 . Thanks for your reply.
I tried with
WeekNum = DATEDIFF (DATE(2019,7,28),Tracker[Start of Week],WEEK) +1
I forgot to mention that the table contains this structure:
Subject Hours Start of Week
A 2 7/29/2019
B 3 7/29/2019
A 2 7/29/2019
B 3 8/5/2019
A 3 8/5/2019
The intention is getting something like:
Subject Hours Start of Week Weeknum
A 2 7/29/2019 1
B 3 7/29/2019 1
A 2 7/29/2019 1
B 3 8/5/2019 2
A 3 8/5/2019 2
The suggestion you sent me is giving me an error indicating that "A single value for the column 'Start of Week' in table 'Tracker' cannot be determined"
Thanks
Ezequiel
You would have to add this as a calculated column in your table, not a measure.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |