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.
I have been using standard DAX code to create a dim_date table. However, the table assumes the first day of the year is always Jan. 1st. In our company, we use International Calendar, which means the first day of year is the first workday of the year. Take 2022 for example, the first work day is Jan. 3rd, hence the 1st week of 2022 starts from Jan. 3rd, Jan. 1st is consider week 52 of previous year. See international calendar attached.
Is there a way to create a dim_date table using the international calendar format?
Solved! Go to Solution.
create column weeknum('dim_date'[Date], 21 )
How to adjust the Year of the calendar, in this case, Jan. 1 2022 becomes last week of 2021, i.e., yearweek should be "202152". Is there an automatic way to update the year?
do you have a column with the date beginning the week? if not create it with power query
pick the year from this column and concatenate the two columns : year('dim_date[datebeginningtheweek])&''week"&weeknum('dim_date'[Date], 21 )
The calendar is created in DAX, how can I create a week start column in Power Query?
I managed to update the year via a Dax formula. However, it will be helpful if you can share with me a video showing how to create calendar table or columns in Power Query.
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 |
---|---|
114 | |
105 | |
79 | |
68 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
82 | |
70 |