skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile

By Avi Singh [Twitter]

If you want to look for trends based on weekday (Sunday…Saturday) or month-of-the-year seasonality (January…December), Cycle Plots can be a potent visualization tool. Some clever folks thought of this back in 1978, but my education on cycle plots has been from Naomi Robbins’ excellent paper. This question was asked during one of our Q&A sessions for the Online Class (Next Class scheduled for Aug 3-4). In this post I’ll discuss the Cycle Plot and then we would build it step by step using Power Pivot. Here is the end result we will achieve (animated gif):

Cycle Plot showing Weekday values for each Week Number and the Average. A slicer also lets the user select the weekday chart should start at

Looking for Periodic Trends

We’ll use sample data showing eight weeks worth of web traffic and we are looking for trends based on the weekday (Sunday…Saturday).

Try 1: We might plot Visitors by Weekday. While this does show the overall pattern of visitors across weekdays, we have no information of how visitor count is changing over those eight weeks.

Try1: No information on how visitors trend changes over the eight weeks

Try 2: We can plot visitors by date. This shows a trend over time and the cyclical pattern is apparent. However, it is hard to track the performance of a single weekday, say Monday over the eight weeks.

Try 2: Hard to track a single weekday (say Monday) over the eight weeks

Try 3: We plot by weekday but add Weeknum as a series. This has a lot of information coded in the graph, but that is also the problem. Viewer is overwhelmed and it is hard to look for the patterns we want to see.

Try 3: A little too much information

Cycle Plot

A Cycle Plot would show data for each weekday broken by the Weeknum. With the same data now rendered as Cycle Plot, you can see the trend for each weekday and see them in relation with other weekdays. Insights just start hitting you on the head!

Cycle Plot: Click to enlarge, see numbered insights below

1) Friday is the peak traffic day and Mon/Tue are the low traffic days.
2) Thu shows strong growth, especially in the recent weeks
3) Fri shows steady growth
4) Sunday shows a gradual decline (except for last week) and may need closer monitoring

Building a Cycle Plot Using Power Pivot

Data Set: We will be using a very simple dataset, but the same steps would apply for a real data set. The added benefit being the “Define Once, Use Everywhere” benefit of Power Pivot.

“Define Once, Use Everywhere” with Power Pivot

Once you define the measures to build the Cycle Plot Pivot and Chart, you would be able to slice and dice it using any of the columns available in your data model. For example, if you build a cycle plot using Sales data; you can analyze it by your Product Category, Country/State (Geography), Customer attributes etc.

Sample Data Set: VisitorCount Table, Calendar Table

You can build a basic pivot by adding Weekday and Weeknum on rows as below and a very simple measure.


This simple pivot might do the trick for creating Cycle Plot

But when you insert a chart for this Pivot Table, it does not quite render the way we want it to for a Cycle Plot.

Excel joins the line across the Weekdays, totally ruining the effect of the Cycle Plot

Cycle Plot Step 1: Inserting a Gap in the Line Chart

To insert a gap, we would want to show an additional Weeknum past the data values shown, but have the “Visitors” measure be blank.

To break the line chart, we need to show an additional blank value

For this we will define a Dummy Measure which will return 0 for that additional Weeknum that we want to show and blank for everything else. That would be sufficient for us.

Define a dummy measure to force the additional Weeknum to show

VisitorsPriorWeek is an intermediate measure we use to create our dummy measure (BTW, it just uses the pattern of the Greatest Formula In The World a.k.a. GFITW! Also tweaked here). Here are the measures:

VisitorsPriorWeek :=CALCULATE (
ALL ( Calendar[WeekNum] ),
Calendar[WeekNum] = MAX ( Calendar[WeekNum] ) – 1

Visitors_Dummy :=IF (
ISBLANK ( [Visitors] )
&& NOT ( ISBLANK ( [VisitorsPriorWeek] ) ),

We clean up our pivot and change the options for the “Dummy” Series on the Chart so as to hide it completely from the display.

Hide the Dummy Measure from Display and we have a clean start towards Cycle Plot

Cycle Plot Step 2: Showing the Average Line

You might have noticed on the cycle plot image we showed at the top that it also shows a line indicating the average Visitor count for that particular weekday. We will add that line using the measure below.

AVERAGEX ( VALUES ( Calendar[WeekNum] ), [Visitors] ),
ALL ( Calendar[WeekNum] )

AVERAGEX being an iterator, we are essentially instructing it to go through all the Calendar Weeks and average the [Visitors] count across them all. Here is the result:

Cycle Plot now showing the average line (click to enlarge)

Cycle Plot Step 3: Rotating the Chart to Start at a User Selected Weekday

Naomi Robbins’ paper said the following was a “nice-to-have”

It is sometimes useful to rotate the subseries in a cycle plot; that is, to start the subseries at a value other than January for months or Monday for days. If your main interest is in peaks in the months December through February, then it would be better to begin around July so the months of interest appear together in the center of the figure.

Well, I just had to build that into our model 🙂
In the animated GIF below you can see that user can select any weekday and the Chart adjusts to “start” at the user selected weekday.

Our slicer lets the user select the weekday chart should start at

This post is running a bit long, so I will point out that this technique is based on “Disconnected Slicers – Part 4” Module in our Online University. Our existing students can review the module for more explanation on this technique.

Using Slicers to change sort order in Pivot? Heck yeah!

Others can download and examine the file using the links below.
Cycle Plots.xlsx

Power On!
-Avi Singh

Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI.

This Post Has 14 Comments
  1. For Part 4. To Sort your chart by the slicer:
    1) Right-click the horizontal axis and select Sort & Filter from the Ribbon and choose Custom Sort.
    2) Ascending by “SelectedCalendarWeekdayOffset”

    This will allow the day in the slicer that is selected to show first on your pivotchart.

    1. Good suggestion. However I prefer to employ a “PowerPivot” solution whenever that exists (without excessive heavy lifting), since that is durable – it would persist even if I (or someone else using the model) built another Table/Chart, heck even if I used a different tool like Power View or reports and dashboards.

      1. The solution persists beyond the individual user. I was filling in a small blank that is missing at the end of your post. This plays a small role in replicating the chart changing when selecting the slicer.

  2. Great post! Insight is usually just finding a way to display the information in the most useful way.

    Perhaps I am cheating, but I have always created these charts using a second day of Week column (add column WD2 to the calendar table with a formula of =[@Weekday]). Then create the chart just above step one, but add the WD2 column to the Legend field, and hide the legend. Bonus points for visual differentiation between the days of the week. Works for any category type, not just dates.

  3. I have a question. Considering I get exactly the sames results for the average if I use
    CALCULATE (AVERAGE([Visitors]), ALL ( Calendar[WeekNum] ))
    – what’s the benefit of using AVERAGEX here?

  4. By chance does the VisitorsPriorWeek measure work in 2013? I can’t seem to get this measure to work properly. It is certainly bringing down the correct value from the week prior but i can’t seem to get it to kick in the new line as it does in 2010. Am i missing something?

    1. I’m having the same problem in 2013, using month-year. The formula doesn’t create year 2016 automatically as far as I can tell.
      I’m doing everything in the same table rather than having a separate calendar table (my source data runs from 2008 to current) so perhaps that makes a difference?

  5. I loved reading the blog, thanks for sharing Avi. I do, however, have two problems which I think makes the cycle plot fall short and become unusable.

    Firstly, when one uses slicers to only select a portion of the calendar period then the dummy value no longer works. For example, selecting only “month 1” in the example workbook (I added the month column in the calendar table) causes the line graph to become a continuous line graph rather than having breaks as it is supposed to. If you clear the month slicer then it works again.

    Secondly, I struggled to get the dummy measure to return the 0 for the extra row when I used datetime values that have fixed number of variables. For example, if one wanted to do a cycle plot by “Week Day Number” (ie Mon = 1, Tue = 2) then I seem not to be able to get the dummy measure to return a 0 for the row that is one more than 7 (I think because there simply are only 7 week day numbers). In Avi’s example he uses week numbers and the dummy measure returns a 9 because week numbers do continue past 8.

    I dearly hope there is a way around both of these problems. Any ideas?

    1. Hi Sven,
      using Power Query for this task would make this solution quite flexible.
      Only flaw: You need to write a macro that refreshes the results automatically once you change the slicer selection of your months. Otherwise you’d have to refresh manually in order to recalculate your PQ queries.
      If this is of interest – have a look:!11788&parId=DE165DDF5D02DAFF!107&app=Excel&wacqt=undefined

      1. Hi Imke. Thank you so much for sharing your very clever solution with me. It worked perfectly. I’ll leave the macro (auto refresh) for another time, as the manual refresh works fine for now. Thanks again!

Leave a Reply

Your email address will not be published. Required fields are marked *