1

I want to plot a graph over a year+weeknumber on the x axis.

Each data-point contains this specific value; for example week 7 of 2016 is expressed as 201607 etc. and called YearWeek

I created a date table in which I calculate all possible YearWeek value in a certain date-range. Then I created a YearWeek table extracting all distinct YearWeeks.

This I connected to the Fact-Table. What I want to chart is exactly according to this matrix: Using a measure to express values over 2 columns, with Yearweek values from the YearWeek Dim table as row context

Note that I explicitly selected to show items with no data to obtain the full time line. It continues down to 201852 but you get the picture.

When I attempt to plot this, it results in this: enter image description here

It's hopefully clear that the straight lines running from 201652 to 201700 and 201752 to 201800 are the problem.

There's three things to note:

  • I explicitly need to keep the x-axis continuous, no gaps in the plots or x-axis values skipping several weeks for lack of data.
  • PowerBI somehow does not want to accept that these values count to 52 and then continue in the next year and decides to make the values strictly numerical despite these values not existing in the YearWeek dimension table.
  • If I change the values to text, PowerBI recognises that these are distinct categories, but it won't provide a continuous axis, just the values for which there is data.

enter image description here

I've tried to connect the YearWeek Dim table to an actual Date Dim table hoping that time intelligence would kick in; the problem is that both for the Fact Table and the Date Dimension table the YearWeek Dim is the unique value which won't work given the filter-direction. If I start messing with many to many relationships or bi-directional filtering I'm out of my depth.

How to fix this?

Sambo
  • 113
  • 1
  • 3
  • 12
  • As far as I know this is not possible: if you use numeric labels you always have the long straight lines between weeks 52 and 00, if you use text labels the line will not be continuous. A possible workaround would be to place 2 charts on top of each other, chart 1 as your image above but with an index instead of weeks and a hidden x-axis, chart 2 only showing the x-axis with weeks. Could be hard to properly align the labels though. – Christian Welsch Jul 16 '18 at 13:00
  • Instead of using only numbers, perhaps use text as well, e.g. 2018w02 – Degan Jul 16 '18 at 20:08
  • @ChristianWelsch This would make a very unelegant solution especially considering that this yearweak is the main time dimension used in all of my reports. When using text labels the chart only shows yearweeks that contain data so the axis won't be continuous. – Sambo Jul 18 '18 at 06:36

0 Answers0