Forum Discussion
Making a sequential Week of year Number
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
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
1 Reply
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