Different streaks of the same column in one chart

[kordnas]

Hey guys,

Here is the thing I'd like to do. Let's take a measure, say total downloads. I'd like to compare downloads from one week to another by overlaying them in one chart (Droplr • Goal chart.png). The numbers come from the same table and the same column.

If you could hint me towards a good way to implement this, that would be awesome. Most important is usability and performance. Not so much ease of implementation

Aaaand: ideally I would like to be able to do this for any odd metric. So I reckon simply duplicating the data is not the way to go. Especially considering that overlaying more than two weeks at once would be really awesome

Thank you for your input!

Sandro

P.S. I am using Excel 2010

 

[JavierGuillen]

This is something that is definitely 'doable' in PowerPivot. The first thing that is needed, though, is to specify *what* are the two weeks that are been compared. Is it the most current and previous week? or is it a selected week and the week before that?

Also, if you can provide some sample of what your data looks like, that would help.

 

[MD610]

I agree with Javier in that we would need to know exactly which weeks you want to be able to select in the final report and how you would like to ultimately interact with the slicers.

For a quick and dirty solution...

I assume your table must have a date column, so the next step would be to create a calcuated column using the WEEKNUM function which will tell you the week of the year that date falls in. Then you could use the WEEKDAY function to get the day in the week into a calculated column.

Then create a new chart from PowerPivot. Drop the WEEKDAY field into the Axis fields. Drop your measure into into the Values. Drop your WEEKNUM field into the Legend Fields and then drop your WEEKNUM field a second time into one of the slicers.

Now you should be able to select any 2 weeks (or 3 or 4 or 5...) by holding down Ctrl when selecting them in the slicer. You could switch out to a different measure by switching out the Values or creating a duplicate chart with the different measure.

Like I said, this is quick and dirty. There are an endless number of ways you could customize something like this and select the weeks. You could even set it up so that you have a single chart where you can select different measures with a slicer and the chart automatically adjusts accordingly to the selected measure.

 

[kordnas]

Hey guys,

Thank you for your responses!

I wanted to keep my requirements regarding the solution as minimal as possible, but I totally see that something more tangible for you to work with would make it easier. I'll create a sample workbook on Monday (when I am off the iPad and back onto the desktop computer) and let you know more details.

Will also try out the approach suggested by MD610 and see how that goes. Again, great to receive such competent and quick help from the community!

Regards,
Sandro

 

[kordnas]

There is a date reference table in my workbook, with which I work to calculate the weeknumbers, day of the week etc. I included it in the sample workbook as well.

I tried it out and have to say that what MD610 suggested already was a solution, that I hadn't thought of, yet. Great, thanks for that! It also is, prototypically, included in the sample workbook.

With that solution though, we are tied to comparing exactly whole weeks (could of course be month as well). What I'd like to have is a bit more flexibility. There is an events table in the sample workbook. It describes what I have changed and on which date. Naturally, I'd like to compare how things went before and after that change. Ideally I'd like to select one of those events in one slicer (only one) and then see something like two weeks before that event in one series and the two weeks after it in the other. Or maybe the aggregate of downloads from the two weeks before and the aggregate of downloads in the two weeks after. Maybe combined with another aggregate measure over these weeks. Something like that...

When I then change the event slicer, the whole thing should update and show the data for the respective timeframes for relative to that date. Does what I wrote there make sense? Let me know

Regards,
Sandro

P.S. Sample workbook link

 

[MD610]

Sample File Solution

See my solution. I added measures showing the weekly average for 2 Weeks Before and 2 Weeks After. I also added measures showing the actual value of the single day 2 Weeks Before and 2 Weeks After. All of it filters based on the Change you select from the Events table. My pivot can be found on the PivotSolution worksheet. This was done with a couple calculated columns to figure out the average downloads per week in the given timeframes and then a couple simple measures to pull everything together.

I first added Calendar Weeks to the Downloads table using the related() to simplify things for myself.
Calculated Column 1 (Avg. 2 Weeks Before) =round(calculate(average([DownloadData]),all('Downloads'),'Downloads'[Week]=earlier('Downloads'[Week])-2),2)
Avg. 2 Weeks After would be the same formula above but change the "-2" to "+2".

For the actual day 2 weeks prior, I used this measure:
2 Weeks Before:=calculate([SumDownloads], DATEADD('DateReference'[Date],-14,Day))
For 2 Weeks After, again, just change the "-14" to "14".

Let me know if you have any questions.

 

[kordnas]

Hello!

What I didn't understand, yet, is why you created a measure "SumDownloads". Seems to give exactly the same result as dragging the "DownloadsData" into the table. Same actually for the measures for the 2 week averages

It seems we had a missunderstanding on what I meant by "two weeks before that event in one series and the two weeks after it". I meant the graph of the downloads, not the aggregate or average. Can we solve this in a similar way?

Also I just tried to create a measure on an additional WeekRange table, which would allow me to adjust the time, that I look forward and backward into the future with a slicer. But when I tried to use this measure in the "Avg 2 weeks before" formula, it said, that I can't do that. Is there another way to achieve this?

Thank you!

Sandro