Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |