Trend by Week of Year

[rayishome]

Seems like an easy concept to solve, but it's proven to be harder than expected.

Options I've Tried:

1. Date Hierarchy:  This doesn't have a weeks level
2. Use WeekNum:  This works great until you cross years
3. Creating a Column to concatenate Year and WeekNum as a string: Trendlines are not an option for strings
4. Creating a Column to concatenate year and WeekNum as a Int: When crossing years the continuous value does not understand 201652 and 201701 are next to each other creating gaps.

The goal is to create a line chart that provides a Trend Line and a Forecast summarizing by Week of the year that will span multiple years.  

Thanks

[dkay84_PowerBI]

One option you haven't tried is to make your own date hierarchy.  Simply drag Year, Qtr, Mnth, Week etc. in order to you axis field and see if that allows you to view the weekly trend without the other issues you described.  You will have to expand your axis (part of the drill down experience) and it should keep track of the levels of the hierarchy as part of the axis labeling.

[rayishome · Accepted solution]

Thanks!  The Hierarchy didn't work as it's switched from Continuous to Categorical.  However, using Query: Week = Date.StartOfWeek([Date]) did create a grouping level by week I was able to use.  Thanks

[rai9micm]

This worked very well. It was so clever and elegant, thank you! 

For those wondering, you can just add the new date into the axis field.

The sad part is that we now don't get the Week Number in the Axis, but got it in the Tooltip.

[SpaceCommander]

I ran into a similar challenge and wanted to share my solution. I have a [Date] column which I want to group by weeks across multiple years and use for chart axes.

First created a calculated column WeekNum = weeknum([Date]). On the chart place the [Date] field on the axis, using date hierarchy and keeping only Year, then place WeekNum underneath. It was sorted incorrectly at first. I had to sort multiple times before the desired order came up (oldest <--> newest).