Column chart by Week Number issue

[MarkSL]

Hi,

I have a Line and Clustered Column chart, which charts by sales by Week Number since 01/01/2018.  This was fine for all of last year, but now that we are into 2019, Week 1 & 2 from 2019 are of course being merged into 2018.  I therefore need to update my chart to somehow differentiate between 2018/9 whilst still showing the Week Numbers and trend in one single view.

Week Number was coming from my DateTable as was define as:  "Week No", WEEKNUM([Date]),

This is how it looks currently, where Week 1 is now including 2018 and 2019:

I have tried adding Year into my 'Week No' calculation, thus making it:  "Year Week No", YEAR([Date]) * 100 + FORMAT(WEEKNUM([Date]),"00")

But this results in the below and my X-Axis is less descriptive and I get the large gap in the data for 201854 through to 201899:

And if I change the X Axis Type to Categorical, I get closer to what I want, but it is no longer in one view:

Ideally I would like to see two X Axis descriptions (Year and Week Number), like what you can get with the new Date hieararchies, however these seem to only work with Year / Quarter / Month / Date.

Any ideas or feedback on how others have charted Week Number across multiple years greatly appreciated!

Thanks

Mark

[v-yulgu-msft · Accepted solution]

Hi @MarkSL,

You could place both [Year] column and [Week No] column onto shared Axis, then, drill down the chart to "Year-Week No" level.

Best regards,

Yuliana Gu

[Anonymous]

I don't know about the initial author, but this definately did help me with the same question. 🙂

[sturlaws]

Hi Mark,

you could try to add a start-of-the-week-date column in you date table. It could be achived with something like this, based on your date column:

start of week date = DATEADD(DateTable[date]; (WEEKDAY(DateTable[date];2)-1)*-1;DAY)

Use DateTable[start of week] on the axis, and you will have something that behaves nicely, although it will not show the weeknumber. You could put weeknum on the tooltip of the chart(but you need to treat it as a measure).

Regards,

Sturla