Making a sequential Week of year Number

[LiamBennett]

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

[OwenAuger · Accepted solution]

@LiamBennett 

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