Weekly Report, Year on Year - Last 8 next 4

[braybrookj]

Hi guys,

Context:

Need some help figuring out how to provide a report which shows the previous 8 weeks as well as the next 4 weeks year on year.

What I've already done:

Using teh week number, I've created a calculated column in my date table with a true/false to identify the week number of the last 8/next 4 weeks regardless of year. I can then use the week number on an axis and the year as the legend.

The problem:

This has worked fine up until december. Now the "next" weeks are in the new year and the week number is lower than the current one and these are displaying at the wrong end of the graph with a gap to the "last 8 section".

How do I get the axis to flow from week 50 - 51 - 52 - 53 - 1 - 2 - 3 etc?

Secondly, is there a way to prevent the year line connecting to itself? week 53 2016 joining to week 1 2016. The line is the same colour and would be confusing as the week 53 2016 should join week 1 2017... Alternatively, I wouldnt mind a gap being put in with a "week 0" with no data so that a gap is forced between the years.

Any suggestions?

Thanks

[CheenuSing]

Hi @braybrookj

Please refer to the solution posted by me under

http://community.powerbi.com/t5/Desktop/Dates-in-Power-Bi-Next-4-weeks-etc/m-p/44569#M17212

This should help ypou achieve.

If it works please accept it as a solution and also give KUDOS.

Cheers

CheenuSing

[braybrookj]

Hi @CheenuSing

Appreciate the response, however I don't think your solution fits my problem.

I've solved my first problem by creating a week dimension to compliment the date table to match the week number with a 2017 week start date so that I have a common week start date for each year.

My second problem still exists where the 2015 data flows on to the 2015 data when it should join to the 2016 data. See image below:

Any suggestions on how to make the yellow section dissappear?

Cheers

[v-caliao-msft]

Hi @braybrookj,

You could use Year and Week Number on the X-axis. In your dataset, create a calculated column by using the DAX below.
WeekNumber = YEAR('Table 2'[Date])&right("00"&WEEKNUM('Table 2'[Date]),2)

And then change data type from text to whole number.

Regards,

Charlie Liao

[braybrookj]

HI @v-caliao-msft

Correct me if I am wrong, but if I use a combination of year & week I would only get one line rather than two? My aim is to show the year on year trend.

Thanks

[CheenuSing]

Hi @braybrookj

Could you please share your data model and expressions used to derive a solution.

What is the x-axis and y-axis values you are plotting.

Cheers

CheenuSing

[braybrookj]

Hi @CheenuSing

Unfortunately, I cannot share the model due to sensitive data.

My x-axis is using a week dimension to give me the week start dates of 2017 against all weeks from 2015 - 2017 inclusive.

y-axis shouldn't make a difference. It's just an arbitrary percentage we are monitoring as a KPI.

Thanks

[CheenuSing]

Hi  @braybrookj

Appreciate your concern. Is it possible to share from your date table, the values of year, date and weeks of the years.

We have to bear in mind the week numbers may not start on the same day for each year. I see that you are plotting by date than week number. If you plot the x-axis as WeekNumbers and Years as legend it may help.

Let me know if this leads you to something. 

Cheers

CheenuSing

[braybrookj]

hi @CheenuSing,

My date table is fairly generic with week number calculated using WEEKNUM( 'Date'[Date], 2 ).

I have added a week table -image below- joining on the week number so that I can display a week start date rather than a week number (having numbers 1 - 52 along the axis isn't particulalry user friendly if you dont have a calendar to reference)

The week start date in this table applies to 2015, 2016 and 2016 so using week number or my 2017 week start date will not make a difference. (The sort column allows the flow over new year to appear in the middle of the graph).

The issue here now is the fact that week 53 2015 in my graph joins to week 1 2015 and not week 1 2016. This either needs to be corrected or I need a way to blank the line between and stop the years joining...

Cheers

[jblackshear]

I just came across this article which may be relevant to what you are trying to accomplish.

http://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/