Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Everyone,
I learnt to create a calenar table using DAX and would like to learn about how to assign numbers to each week for reporting purposes. Below is the table I have created.
In this table, I'd like to add a column showing week number. For example, from 2023-05-24 to 2023-05-31 is Week 1. Only week 1 is from a Wednesday to Wednesday. From Week 2 and onwards, I'd like to have the weeks running from Thursday to Wednday. That being said, Week 2 is 2023-06-01 to 2023-06-07. Week 3 is 2023-06-08 t0 2023-06-14 etc.
Does anyone know how to write a DAX to achieve this result?
Thank you in advance for your help!! 🙂
@kathyyy19 , do you want start you week always from 1st jun and merge week 53 in 52 ?
First have year start date like
Start year = Date( if( month([date]) <6, year([date]) -1, Year([date])) ,6,1)
Then have week like
Week =
var _week = quotient([Start Year],[Date],day),7)
return
if(_week <=52, _week, 52)
Week That Resets Yearly
https://community.powerbi.com/t5/Community-Blog/Week-That-Resets-Yearly-Decoding-Date-and-Calendar-3...
Hi Amit,
Thanks for your comment. I'd like my first week starting from May 24 2023. First week is running from 2023/05/24-2023/05/31. Second week= 2023/06/01-2023/06/07. Third week=2023/06/08-2023/06/14 etc. Can you show me how to achieve this please?
Thank you Amit! I will try your method!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |