Doing Time Intelligence in Power BI - A Review Case

This article is a brief review about how to develop Time Intelligence models in Power BI.  That said, this post is written for the reader who is new to the concept.

Time intelligence is computing the standard used in calculations over periods of Time or Dates.

Typical examples of Time Intelligence:

Aging

Aging is to break down calculations over periods of time.  A noteworthy example is the Aged order backlog that renders 'open customer orders' by shipping date or an original acknowledge the date.

Period to Date

Period to date is the sum total of a metric during a period.

 

*        Revenue Quarter to date

*        Inventory Receipts month to date

*        Quarter to date Mobile phone costs

Comparisons

Comparisons juxtapose two similar periods of time for a given metric. Almost always these comparisons are outlined as a percentage.

*        Year over Year Sales

*        On-time performance vs. the previous month

*        Standard hours earned vs. the previous quarter

*        YOY – Monthly Sales %

Key Component of Time Intelligence (TI)

Calendar

It is a given to generate a calendar when you are creating a report in Power BI.  It technically isn’t required anymore in every case since Microsoft will now create a simple Gregorian calendar in the background if you turned on the feature.

If you have more complicated calendars for instance a 4 - 4 - 5 calendar (4 weeks,

4 weeks, 5 weeks) or Australia tax calendar (Year starting July 1) you will have to generate one to perform Time Intelligence calculations.

Requirements for a Calendar Table

*        One entry/line for a single day in the period you look to cover.

*        Critical Periods which you aim to do the measurement against (Month to date, Quarter to date, Year to date)

Method to build custom calendars

Power Query (M-Script) based Calendars

Power Query has a great function called List. Dates in its library let you build calendars out of nowhere.

Following is the syntax:  List. Dates (start as date, count as a number, step as duration) as list.

Deploying PowerQuery to expand ERP calendars

In several ERP systems, the calendar is one line per period.  It will have the Year, the fiscal period, and the start and end date of the period.  Although you can’t use this directly in your model, through the deployment of Power Query you can expand this out to a proper calendar.  This method is a smart way to ensure one version of the truth.

Final Word

It is highly recommended to spend the time to put in place your calendar accurately when building your model in Power BI.