Deneb/Vega-Lite can be used to easily compare temporal periods. The example presented herein includes a smoothed line chart with two background highlight rectangles and a comparison KPI card with four metrics. Two cases (monthly and quarterly) are used. While the line chart and background rectangles are simple, the comparison KPI cards are a bit more involved, with the data shaping and presentation code comprising about half the JSON in each case. Two options (vertical and horizontal) for the comparison KPI cards are included.
NOTE:
Special thanks to @Gustaw for his review and improvement suggestions and to @dm-p for his assistance with the window/lead/offset transformation syntax.
While the code for the quarterly visual is mostly consistent with the monthly code (except for the comparison KPI card), the following describes the code for the monthly visual, which illustrates a number of Deneb/Vega-Lite features, including:
0 - General:
- a “title” block with subtitle
- a shared “transform” block to extend the dataset in-visual with:
- a “filter” transform to limit the data to the selected year parameter
- 3x “calculate” transforms to determine year, month, and day
- a “calculate” transform to determine the year-month key
- a “params” block with:
- a “range” parameter to allow the user to select the year of interest
- 2x “range” parameters to allow the user to select the periods of interest
- a shared “encoding” block to ensure consistent axes are used for all visuals
- a “layer” block to overlay the background rectangles, the line chart, and the comparison metrics
1 - Background Rectangle (Period 1):
- a nested “transform” block with:
- a “joinaggregate/min” transform to determine the period 1 minimum date
- a “joinaggregate/max” transform to determine the period 1 maximum date
- a “filter” transform to restrict the dataset to a single record for the month of the selected period 1 of interest
- a nested “layer” block with:
- a “rect” mark in dark blue (Pantone Colour of the Year 2020) and 50% opacity with x-only values ranged from the period minimum to the period maximum
- a “circle” mark in black and full opacity offset from the minimum period date
- a “text” mark in white and full opacity (P1)
2 - Background Rectangle (Period 2):
- a nested “transform” block with:
- a “joinaggregate/min” transform to determine the period 2 minimum date
- a “joinaggregate/max” transform to determine the period 2 maximum date
- a “filter” transform to restrict the dataset to a single record for the month of the selected period 2 of interest
- a nested “layer” block with:
- a “rect” mark in dark grey and 25% opacity with x-only values ranged from the period minimum to the period maximum
- a “circle” mark in black and full opacity offset from the minimum period date
- a “text” mark in white and full opacity (P2)
3 - Line Chart:
- a “line” mark in black using “monotone” interpolation for smoothing
4 - Period Comparison Metrics:
- a nested “transform” block with:
- a “filter” transform to restrict the dataset to those records in the two selected periods (months)
- 4x “joinaggregate” transforms to determine the min, avg, max, and sum of the two selected periods
- a “fold” transform to pivot the dataset around the min, avg, max, and sum columns
- a “filter” transform to restrict the dataset to the records for the first of the month
- a “calculate” transform to create a new “ID” column
- a “window/lead” transform to get the 4th offset record using the new “ID” column
- a “filter” transform to retain only the first 4 “ID” values
- 4x “calculate” transforms to determine the variance, percent variance, metric label, and metric Y coordinate
- a nested “layer” block with:
- a “rect” mark with rounded corners in grey (separate 0-50-0 gradient colours for the fill and border) for the “KPI card”
- a “text” mark hard-coded to the card title (period comparison)
- 5x “text” marks hard-coded to the column header values (metric, p1, p2, var, % var)
- a nested “layer” block with:
- a “text” mark for the metric label (minimum, average, maximum, total)
- 4x “text” marks for the metrics (period 1 value, period 2 value, variance, % variance)
The intent of this example is not to provide a finished visual, but rather to explore the use of the Deneb custom visual and the Vega-Lite language within Power BI and to serve as a starting point for further development.
Also included is the development PBIX using data from the Microsoft Contoso sample database with dates adjusted forward by 14 years.
This example is provided as-is for information purposes only, and its use is solely at the discretion of the end user; no responsibility is assumed by the author.
Greg
Deneb Example - Period Comparison - V10.pbix (1.5 MB)