Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
---|---|
76 | |
75 | |
40 | |
27 | |
24 |
User | Count |
---|---|
96 | |
92 | |
52 | |
46 | |
42 |