skip to Main Content

Post by Rob Collie
 
Blending “Time of Day” Analysis with Calendar/Date Analysis in Power Pivot and Power BI

Our “Morning” Website Traffic is Down 21.5% in Jan 2014 vs. Jan 2013, But “Evening”
Traffic is Up by a Similar Amount, and Full-Day Traffic is “Flat” at +0.9%

(Fake Data, But Real Analysis)

Two Different Flavors of “Time”

Usually, when we talk about “time” in Power Pivot, we’re talking about the Calendar/Date flavor:  How much have things changed from yesterday to today.  What are our Month to Date numbers, and how do those compare against the same period last year?  Let’s call this “macro-trending.”

But time of day is also often interesting:  what are the trends WITHIN a day?  Let’s call this “micro-trending.”

And then, the hybrid of the two:  how are our “micro” trends changing over the course of the year, month, quarter, etc?

I don’t think the techniques here are terribly complicated, but they might be a little difficult to conjure up on your own.  So, it’s time for a post – and a downloadable workbook! Smile

The Key:  Separate the Date and Time Components!

Let’s say your data comes in looking like this:

Blended Date and Time (DateTime data type) in a single column in Power Pivot - not useful

Source Data:  Each Entry is Stamped with a Single Value of Blended “Date/Time” Type

When we see something like this, alarm bells should go off.  We can NEVER work with a “blended” date/time column like this.  It’s not going to work, period.  Trust me. 

So our first step should be to “split” this column into separate columns of “Pure Date” and “Pure Time.”

Blended Date and Time (DateTime data type) in a single column in Power Pivot - Splitting it Into Useful Components

Using Calculated Columns, We “Split” the original Column into a “Pure Date,”
and a “Pure Hour of Day”

The formulas for those calc columns are:

[Date]:=

  DATE(YEAR([Date Time]), MONTH([Date Time]), DAY([Date Time]))

[Hour]:=

  HOUR([Date Time])

A Note on Performance, and Alternatives to Calc Columns

The table of Session data pictured above is a CLASSIC example of a Data Table.  Data Tables (aka Fact Tables) are a concept explained in the book and in PowerPivotPro University, but the short version is that Data Tables often get… big.  Calculated columns in “big” tables tend to be one of the prime contributors to workbook size and slowness, so I tend to discourage this practice.

It would be much better to perform this split in the original data source, and not even import the “blended” column in the first place.  That eliminates the need for calculated columns, AND reduces your overall column count by one (number of columns is ALSO a big contributor to workbook size and slowness).

Similarly, it would be much better to perform the split using Power Query.  But if you plan to schedule these workbooks for auto-refresh on a server someday, that might be problematic, because right now, Power Query doesn’t run on any server except the Power BI Cloud servers from Microsoft.

So, the “lowest common denominator” approach is to use calc columns as specified above.  If your workbook gets too big or too slow, then try splitting the columns in the original database etc. – OR if you aren’t worried about the Power Query autorefresh limitations, by all means use Power Query to do it.

(These performance implications of calc columns, number of columns, etc. are repeatedly one of TOP the things students and readers gush about with respect to the book and PowerPivotPro University – forgive my shameless plugging here, but it’s the absolute truth.  If you lack that knowledge, sooner or later you owe it to yourself to acquire it, regardless of method.)

Next Up:  Calendar and “Time of Day” Tables

Now that we have those two columns, we use them to create relationships to Calendar and TimeOfDay tables:

Calendar/Date Lookup Table AND a Time of Day Lookup Table - in Power Pivot / Power BI

Separate Lookup Tables for Date (Calendar) and Hour (TimeOfDay)

image

Calendar:  One Row Per Day (730 Rows, Since we Have Two Years)

Time of Day Lookup Table - in Power Pivot / Power BI

TimeOfDay:  One Row Per Hour (24 Rows)

Simple, Straightforward Analysis Time!

Put [Month Year] from Calendar table on the pivot, plus a simple [Session Count] measure:

image

[Month Year] from Calendar Table.  [Session Count] measure from Sessions Table.

where [Session Count] is defined as:

[Session Count]:=

  COUNTROWS(Sessions)

Then we can add [Time of Day] from the Time of Day table:

Blending “Time of Day” Analysis with Calendar/Date Analysis in Power Pivot and Power BI

No Special Math Required, Just Clicked the [Time of Day] Checkbox in the Field List!

Now we can add [Prior Year Session Count] and [Sessions % CHG Since Prior Year]…

Blending “Time of Day” Analysis with Calendar/Date Analysis in Power Pivot and Power BI

Added Measures So We Can Compare to Prior Year – Again, Everything Just Works!
(Sliced to 2014, Since 2013 is our first year of data, so the new measures are blank for 2013)

[Prior Year Session Count]:=

  CALCULATE([Session Count], DATEADD(Calendar[Date], -1, Year))

and…

[Sessions % Change vs Prior Year]:=

  DIVIDE([Session Count] – [Prior Year Session Count],
         [Prior Year Session Count]        )

Quick Visual Cleanup

Some conditional formatting, removing some fields, and pivot styling yields:

Blending “Time of Day” Analysis with Calendar/Date Analysis in Power Pivot and Power BI

A More Visually-Polished Result

Done?  Maybe…

What if we want something like THIS instead?

Hourly Trends in Power Pivot / Power BI

Viewing the “Intra-Day” Trend – But Sliceable by Year and Month!

Do to THAT, all we need is a [Sessions per Hour] measure.  If we just used [Session Count], we’d skew the results in favor of Late Night, because there are more Late Night hours in the clock (7) than there are Early Afternoon for instance (3).

This leads us to…

[Sessions per Hour]:=

  DIVIDE([Session Count], COUNTROWS(TimeofDay))

But that’s NOT the formula I ACTUALLY USED!  We want the “Scale” of the chart to be accurate no matter what we select on the slicers.  For instance, right now we are looking at a single month – Oct 2014, which has 31 days in it.

So if we clear the slicers, we’ll now be looking at 730 days rather than 31 days, and we don’t want our numbers to inflate 24x. 

So we modify [Sessions per Hour] to ALSO divide by number of days:

[Sessions per Hour]:=

  DIVIDE([Session Count],
         COUNTROWS(TimeofDay) * COUNTROWS(Calendar
        )

And the chart scale behaves properly at all times:

Hourly Trends in Power Pivot / Power BI

Now we are Looking at ALL of 2014, and the Scale Remains in the 1.5 Range
(Yes, I Should Format the Scale and add an Extra Digit)

Questions?

Did I miss anything you’d like to see?  Post a comment Smile

Download the Workbook

Grab the Workbook (2010 Format) Here

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 6 Comments
  1. If you didn’t have a time table, could you use the switch command to establish periods in the day? Followed by another switch to set the ordering?

    1. Ooh! We need a whole post on this.

      But for now here’s the trick:

      CALCULATE([My Measure], FILTER(ALL(TimeofDay), TimeofDay[Hour]< =MAX(TimeOfDay[Hour])))

Leave a Comment or Question