Power BI Blog: Highlight Event Periods – Part 1

Welcome back to this week’s edition of the Power BI blog series.  This week, we will show you how to label periods on a visual.

Different things may affect sales and revenue.  Holidays generally lead to higher consumption, businesses themselves can host sales campaigns to boost the figures, and unfortunate events can also happen to hurt businesses, such as COVID-19.  So, when creating sales visuals in Power BI, how do we highlight such events?

We simulated a sales dataset for 2020 and selected a few events for the year.  With that, we built the above visual which highlights the duration of an event and displays the event’s name in a label.

We will go through detailed steps to build this visual, and you can download our data workbook and sample file to follow along.

Chart Data

Besides the main dataset, we need details of the events to build the visual.  In our workbook we included the Table Events:

You need to load both the main Table Financials and the Table Events into Power BI.  We created a parameter File_Path in Power Query.  After downloading our data workbook, please change the parameter to your local file path of the workbook.

We also need two [2] copies of the Table Events, one unconnected and one connected to Calendar.  To make a duplicate of the Table Events, we right click on it and choose Duplicate.

Finally, we build a Many to one relationship between 'Financials'[Date] and 'Calendar'[Date], and another Many to one relationship between 'Events'[Start] and 'Calendar'[Date].

The ‘Event flag’ Measure

Now let’s create the first chart measure that can be used to change colours to highlight a period.  The measure is a flag checking whether the current date is related to any events, and we will use the unconnected copy of the Table Events.

The function SUMX executes on each row of the Table Events_Copy, where the CALENDAR function creates a single-column Table containing the dates between Start and End of an event.

The MAX function returns the current date of Calendar.  The logical operator IN checks whether the current date is within duration of the event and returns TRUE or FALSE.

Lastly, the INT function converts the Boolean values (TRUE or FALSE) to one [1] or zero [0].  Then, SUMX sums up all rows of events in Events_Copy.  That means the measure equals to zero [0] if the current date is not contained in any events, and it equals to one [1] if the current date is contained in one [1] and only one [1] event.  The measure can be greater than one [1] if multiple events are overlapping, but we will only be using it as a zero/non-zero flag.

To build the combo visual, we first create a Line and Stacked Column chart, use Month Name and Day as x-axis fields, and use Sales as the column y-axis field.  Here, we have created a simple measure for the sales values:

Then, we insert a Slicer on Month Name.

Now, we can use the measure Event flag that we just defined to highlight the event periods.  Inside the Format pane for the chart, we go to Visual -> Columns -> Colors and click the Conditional formatting button (fx).

Here, we choose the Gradient style and select the measure Event flag.  For Minimum and Maximum, we choose the colours that we want for ordinary periods and periods with events.  Also, we set the Custom values to zero [0] and one [1].  That way, non-zero values equal or larger than one [1] will all be treated as one [1].

Now the Christmas sales campaign from 5th to 28th December has been highlighted, so are other events in the year.

That’s it for this week’s Power BI blog.  We will go through the other chart measures and how to complete the visual next week.  Hope you’ve enjoyed our article, and please stay tuned for more thoughts and insights from http://www.sumproduct.com.