Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a Rolling Calendar in my Power BI dashboard, and I was wondering if someone knew how to make the Week of Year sequential. Hence, week 1 of Jan 2018 will equal 1 and week 1 of Jan 2019 will equal 53 and so on. I have data from multiple years so with weekly targets the weeks with the same week of year as the previous year cause an error in the way I would like to present my data.
Any help would be greatly appreciated. 🙂
Thanks.
Liam
Solved! Go to Solution.
I suggest you take a look at the DAX Date Template over at SQLBI.
In regards to your question, they create a column called YearWeekNumber (plus a couple of variants) which is an index that increments by 1 for each week.
They define it as:
INT ( DIVIDE ( Date - FirstWeekReference ) / 7 ) (pseudocode)
where FirstWeekReference just needs to be any date at the beginning of a week.
In the Date Template they have FirstWeekReference in the range 1900-12-30 (Sun) to 1901-01-05 (Sat) (depending on which weekday is defined as the first day of the week) but you could use any date you like.
You could use a similar definition. Have a look at that and please post back if needed.
Regards,
Owen
I suggest you take a look at the DAX Date Template over at SQLBI.
In regards to your question, they create a column called YearWeekNumber (plus a couple of variants) which is an index that increments by 1 for each week.
They define it as:
INT ( DIVIDE ( Date - FirstWeekReference ) / 7 ) (pseudocode)
where FirstWeekReference just needs to be any date at the beginning of a week.
In the Date Template they have FirstWeekReference in the range 1900-12-30 (Sun) to 1901-01-05 (Sat) (depending on which weekday is defined as the first day of the week) but you could use any date you like.
You could use a similar definition. Have a look at that and please post back if needed.
Regards,
Owen
User | Count |
---|---|
101 | |
90 | |
78 | |
70 | |
69 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |