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