Deneb/Vega-Lite can be used to display a series of columns and labels thus creating a waterfall chart. A year-based example of such a waterfall chart was posted to the forum last year.
Deneb/Vega-Lite can also be used to display a period-based waterfall chart as an alternative. The alternative example presented herein has the following additional features:
- changed from a specific year to general period
- no DAX; all calculations are done within the Deneb/Vega-Lite visual
- simple Power BI dataset, with only Dates[Date] column and [Total Sales] measure being provided outside Deneb/Vega-Lite
- added start date and end date screen widgets to allow in-visual filtering
- added a “period performance” column (at right) complete with coloured vertical arrow
- added horizontal rules for the period “begin” and period “end”
- added layers for the visual elements to “design” overlap
- added white “rectangle” behind each +/- label to increase read-ability (prevent grid lines from obscuring)
This example illustrates a number of Deneb/Vega-Lite features, including:
0 - General:
- use of a “transform” block to extend the dataset with:
- in-visual calculations for previous month amount and x-axis labels
- in-visual filtering based on the selections in the start date and end date screen widgets (i.e., parameters)
- use of a “params” block to enhance the dataset with:
- screen widgets for selection of the period start date and period end date
- variables for the column colours
- out colour: Power BI theme colour = Divergent/Min
- positive colour: Power BI theme colour = Sentiment/Positive
- negative colour: Power BI theme colour = Sentiment/Negative
- use of a shared “encoding” block to ensure the x-axis was identically applied to all layers
- use of a top-level “layer” block consisting of 3 sections: performance, out-period (i.e., begin, end), and in-period (months). Each top-level layer contains several nested layer blocks as follows:
1 - Performance:
- use of a “transform” block to extend the dataset with:
- in-visual calculations for the overall period maximum, minimum, amount and percent variance, and performance label
- in-visual filtering for only a single record (period “performance”)
- use of nested “layer” block with:
- vertical “rule” mark for the arrow body c/w +ve/-ve colouring
- 2x “point” marks for the arrow heads c/w +ve/-ve colouring
- 2x white “rect” marks for the +ve/-ve labels (to improve label legibility by “hiding” grid lines)
- 2x black “text” marks for the +ve/-ve amount and percent variance labels
- 2x horizontal “rule” marks for the period maximum and minimum grid lines
2 - Out-Period:
- use of a “transform” block to extend the dataset with:
- in-visual filtering for 2 records (period “begin” and "end)
- use of nested “layer” block with:
- 2x “bar” marks (in “out” colour) at 95% band width to increase padding between columns
- 2x “text” marks (in bold black) for begin and end amounts (formatted to 3 significant digits in SI units)
3 - In-Period:
- use of a “transform” block to extend the dataset with:
- in-visual filtering for “in-period” months
- in-visual calculations of amount and percent variances, maximum, minimum, and delimited label (for multi-line)
- use of nested “layer” block with:
- “bar” mark c/w +ve/-ve colouring at 60% opacity and 95% band width to increase padding between columns
- 2x white “rect” marks for the +ve/-ve labels (again, to improve label legibility by “hiding” grid lines)
- 2x black “text” marks for the +ve/-ve amount and percent variance labels
In addition, as multi-line labels are used many times in the visual (x-axis, +/- labels), the linebreak character is set once only in the “config” section.
Specification JSON Code
{
"title": {
"anchor": "start",
"align": "left",
"offset": 10,
"text": "Power BI Waterfall Chart (alternate) using Deneb",
"font": "Verdana",
"fontSize": 36,
"fontWeight": "bold",
"fontStyle": "normal"
},
"data": {"name": "dataset"},
"width": 1180,
"height": 670,
"transform": [
{
"calculate": "year( datum['Date'] )",
"as": "_current_year"
},
{
"calculate": "month( datum['Date'] )",
"as": "_current_month"
},
{
"calculate": "datum['_current_year'] * 100 + datum['_current_month']",
"as": "_current_year_month"
},
{
"aggregate": [
{
"op": "sum",
"field": "Total Sales",
"as": "_current_month_amount"
}
],
"groupby": ["_current_year_month"]
},
{
"sort": [
{
"field": "_current_year",
"order": "ascending"
},
{
"field": "_current_month",
"order": "ascending"
}
],
"window": [
{
"op": "lag",
"field": "_current_month_amount",
"as": "_previous_month_amount"
}
],
"frame": [null, 0]
},
{
"calculate": "datetime( slice( toString( datum['_current_year_month'] ), 0, 4), slice( toString( datum['_current_year_month'] ), 4, 6) )",
"as": "_current_date"
},
{
"filter": "datum['_current_date'] >= _start_date_expanded"
},
{
"filter": "datum['_current_date'] <= _end_date_expanded"
},
{
"window": [
{
"op": "rank",
"as": "_date_rank"
}
],
"sort": [
{
"field": "_current_year_month",
"order": "ascending"
}
]
},
{
"calculate": "datum['_date_rank'] - 3",
"as": "_date_rank"
},
{
"joinaggregate": [
{
"op": "min",
"field": "_date_rank",
"as": "_min_date_rank"
}
]
},
{
"joinaggregate": [
{
"op": "max",
"field": "_date_rank",
"as": "_max_date_rank"
}
]
},
{
"calculate": "datum['_date_rank'] < 1 ? _out_period_start_date_parameter : null",
"as": "_out_period_start_date"
},
{
"calculate": "datum['_date_rank'] < 1 ? _out_period_end_date_parameter : null",
"as": "_out_period_end_date"
},
{
"calculate": "datum['_date_rank'] < 1 ? _period_performance_date_parameter : null",
"as": "_period_performance_date"
},
{
"calculate": "datum['_date_rank'] == 1 ? datum['_previous_month_amount'] : null",
"as": "_z_interim_out_period_start_amount"
},
{
"calculate": "datum['_date_rank'] == datum['_max_date_rank'] ? datum['_current_month_amount'] : null",
"as": "_z_interim_out_period_end_amount"
},
{
"joinaggregate": [
{
"op": "sum",
"field": "_z_interim_out_period_start_amount",
"as": "_out_period_start_amount"
}
]
},
{
"joinaggregate": [
{
"op": "sum",
"field": "_z_interim_out_period_end_amount",
"as": "_out_period_end_amount"
}
]
},
{
"calculate": "datum['_date_rank'] < 1 ? datum['_out_period_start_amount'] : null",
"as": "_out_period_start_amount"
},
{
"calculate": "datum['_date_rank'] < 1 ? datum['_out_period_end_amount'] : null",
"as": "_out_period_end_amount"
},
{
"calculate": "datum['_date_rank'] == -2 ? '000-BEGIN' : datum['_date_rank'] == -1 ? '899-END' : datum['_date_rank'] == 0 ? '999-PERIOD' : ( 100 + datum['_date_rank'] ) + '-' + timeFormat( datum['_current_date'], '%b|%Y')",
"as": "_x_axis_label"
}
],
"params": [
{
"name": "_start_date",
"value": "2022-01-01",
"bind": {
"input": "date",
"name": "Start Date:‎ ‎"
}
},
{
"name": "_end_date",
"value": "2023-12-31",
"bind": {
"input": "date",
"name": "End Date:‎ ‎ "
}
},
{
"name": "_min_year",
"expr": "slice( _start_date, 0, 4 )"
},
{
"name": "_min_month",
"expr": "slice( _start_date, 5, 7 )"
},
{
"name": "_max_year",
"expr": "slice( _end_date, 0, 4 )"
},
{
"name": "_max_month",
"expr": "slice( _end_date, 5, 7 )"
},
{
"name": "_start_date_current",
"expr": "datetime( _min_year, _min_month - 1)"
},
{
"name": "_z_interim_end_date1",
"expr": "datetime( _max_year, _max_month - 1)"
},
{
"name": "_z_interim_end_date2",
"expr": "timeOffset( 'month', _z_interim_end_date1, 1)"
},
{
"name": "_end_date_current",
"expr": "timeOffset( 'day', _z_interim_end_date2, -1)"
},
{
"name": "_out_period_start_date_parameter",
"expr": "timeOffset( 'month', _start_date_current, -1)"
},
{
"name": "_out_period_end_date_parameter",
"expr": "timeOffset( 'month', _end_date_current, 1)"
},
{
"name": "_start_date_expanded",
"expr": "timeOffset( 'month', _start_date_current, -3)"
},
{
"name": "_end_date_expanded",
"expr": "timeOffset( 'month', _end_date_current, 0)"
},
{
"name": "_period_performance_date_parameter",
"expr": "timeOffset( 'day', _out_period_end_date_parameter, 1)"
},
{
"name": "_in_colour_positive",
"expr": "pbiColor('positive')"
},
{
"name": "_in_colour_negative",
"expr": "pbiColor('negative')"
},
{
"name": "_out_colour",
"expr": "pbiColor('min')"
},
{
"name": "_out_colour_positive",
"expr": "pbiColor('positive')"
},
{
"name": "_out_colour_negative",
"expr": "pbiColor('negative')"
},
{
"name": "_arrow_size",
"value": 100
},
{
"name": "_in_period_background_rectangle_width",
"value": 40
},
{
"name": "_in_period_background_rectangle_height",
"value": 30
},
{
"name": "_performance_period_background_rectangle_width",
"value": 60
},
{
"name": "_performance_period_background_rectangle_height",
"value": 34
}
],
"encoding": {
"x": {
"field": "_x_axis_label",
"type": "ordinal",
"axis": {
"title": "Date",
"labelAngle": 0,
"labelAlign": "center",
"ticks": false,
"labelPadding": 10,
"labelExpr": "slice( datum.value, 4, 100 )",
"labelFont": "Segoe UI",
"labelFontSize": 14,
"titleFont": "Segoe UI",
"titleFontSize": 16
}
}
},
"layer": [
{
"name": "PERFORMANCE_PERIOD",
"transform": [
{
"filter": "datum['_date_rank'] == 0"
},
{
"calculate": "datum['_out_period_end_amount'] - datum['_out_period_start_amount']",
"as": "_out_period_variance"
},
{
"calculate": "datum['_out_period_variance'] < 0 ? datum['_out_period_start_amount'] : datum['_out_period_end_amount']",
"as": "_out_period_y_value"
},
{
"calculate": "datum['_out_period_variance'] / datum['_out_period_start_amount']",
"as": "_percent_out_period_variance"
},
{
"calculate": "datum['_out_period_variance'] < 0 ? datum['_out_period_start_amount'] : datum['_out_period_end_amount']",
"as": "_out_period_amount_minimum"
},
{
"calculate": "datum['_out_period_variance'] >= 0 ? datum['_out_period_end_amount'] : datum['_out_period_start_amount']",
"as": "_out_period_amount_maximum"
},
{
"calculate": "format( datum['_out_period_variance'], '+.2s' ) + '|' + format( datum['_percent_out_period_variance'], '+.1%' )",
"as": "_performance_period_amount_label"
}
],
"layer": [
{
"name": "PERFORMANCE_VERTICAL_RULE",
"mark": {
"type": "rule",
"tooltip": true,
"strokeWidth": 2
},
"encoding": {
"y": {
"field": "_out_period_start_amount",
"type": "quantitative",
"axis": {
"title": "Amount"
}
},
"y2": {
"field": "_out_period_end_amount"
},
"color": {
"condition": [
{
"test": "datum['_out_period_variance'] < 0",
"value": {
"expr": "_out_colour_negative"
}
}
],
"value": {
"expr": "_out_colour_positive"
}
}
}
},
{
"name": "PERFORMANCE_VERTICAL_POSITIVE_ARROW",
"transform": [
{
"filter": "datum['_out_period_variance'] >= 0"
}
],
"mark": {
"type": "point",
"filled": true,
"size": {
"expr": "_arrow_size"
},
"opacity": 1,
"yOffset": 4
},
"encoding": {
"y": {
"field": "_out_period_end_amount",
"type": "quantitative"
},
"shape": {
"condition": [
{
"test": "datum['_out_period_variance'] < 0",
"value": "triangle-down"
}
],
"value": "triangle-up"
},
"color": {
"condition": [
{
"test": "datum['_out_period_variance'] < 0",
"value": {
"expr": "_out_colour_negative"
}
}
],
"value": {
"expr": "_out_colour_positive"
}
}
}
},
{
"name": "PERFORMANCE_VERTICAL_NEGATIVE_ARROW",
"transform": [
{
"filter": "datum['_out_period_variance'] < 0"
}
],
"mark": {
"type": "point",
"filled": true,
"size": {
"expr": "_arrow_size"
},
"opacity": 1,
"yOffset": -4
},
"encoding": {
"y": {
"field": "_out_period_end_amount",
"type": "quantitative"
},
"shape": {
"condition": [
{
"test": "datum['_out_period_variance'] < 0",
"value": "triangle-down"
}
],
"value": "triangle-up"
},
"color": {
"condition": [
{
"test": "datum['_out_period_variance'] < 0",
"value": {
"expr": "_out_colour_negative"
}
}
],
"value": {
"expr": "_out_colour_positive"
}
}
}
},
{
"name": "PERFORMANCE_POSITIVE_LABEL_BACKGROUND_RECTANGLE",
"transform": [
{
"filter": "datum['_out_period_variance'] >= 0"
}
],
"mark": {
"type": "rect",
"width": {
"expr": "_performance_period_background_rectangle_width"
},
"height": {
"expr": "_performance_period_background_rectangle_height"
},
"color": "white",
"align": "center",
"yOffset": -2,
"baseline": "bottom"
},
"encoding": {
"text": {
"field": "_performance_period_amount_label",
"type": "nominal"
},
"y": {
"field": "_out_period_end_amount",
"type": "quantitative"
}
}
},
{
"name": "PERFORMANCE_POSITIVE_LABEL",
"transform": [
{
"filter": "datum['_out_period_variance'] >= 0"
}
],
"mark": {
"type": "text",
"color": "black",
"fontSize": 14,
"fontWeight": "bold",
"align": "center",
"yOffset": -18,
"baseline": "bottom"
},
"encoding": {
"text": {
"field": "_performance_period_amount_label",
"type": "nominal"
},
"y": {
"field": "_out_period_amount_maximum",
"type": "quantitative"
}
}
},
{
"name": "PERFORMANCE_NEGATIVE_LABEL_BACKGROUND_RECTANGLE",
"transform": [
{
"filter": "datum['_out_period_variance'] < 0"
}
],
"mark": {
"type": "rect",
"width": {
"expr": "_performance_period_background_rectangle_width"
},
"height": {
"expr": "_performance_period_background_rectangle_height"
},
"color": "white",
"align": "center",
"yOffset": 5,
"baseline": "top"
},
"encoding": {
"text": {
"field": "_performance_period_amount_label",
"type": "nominal"
},
"y": {
"field": "_out_period_end_amount",
"type": "quantitative"
}
}
},
{
"name": "PERFORMANCE_NEGATIVE_LABEL",
"transform": [
{
"filter": "datum['_out_period_variance'] < 0"
}
],
"mark": {
"type": "text",
"color": "black",
"fontSize": 14,
"fontWeight": "bold",
"align": "center",
"yOffset": 8,
"baseline": "top"
},
"encoding": {
"text": {
"field": "_performance_period_amount_label",
"type": "nominal"
},
"y": {
"field": "_out_period_end_amount",
"type": "quantitative"
}
}
},
{
"name": "PERFORMANCE_MINIMUM_HORIZONTAL_RULE",
"mark": {
"type": "rule",
"xOffset": -30
},
"encoding": {
"x": {"datum": "000-BEGIN"},
"x2": {
"datum": "999-PERIOD"
},
"y": {
"field": "_out_period_start_amount",
"type": "quantitative"
}
}
},
{
"name": "PERFORMANCE_MAXIMUM_HORIZONTAL_RULE",
"mark": {
"type": "rule",
"xOffset": -30
},
"encoding": {
"x": {"datum": "899-END"},
"x2": {
"datum": "999-PERIOD"
},
"y": {
"field": "_out_period_end_amount",
"type": "quantitative"
}
}
}
]
},
{
"name": "OUT_PERIOD",
"transform": [
{
"filter": "datum['_date_rank'] < 0"
}
],
"layer": [
{
"name": "OUT_PERIOD_COLUMN_BEFORE",
"transform": [
{
"filter": "datum['_date_rank'] == -2"
}
],
"mark": {
"type": "bar",
"width": {"band": 0.95},
"tooltip": true,
"opacity": 0.8,
"stroke": "white",
"fill": {
"expr": "_out_colour"
}
},
"encoding": {
"y": {
"field": "_out_period_start_amount",
"type": "quantitative",
"axis": {
"title": "Amount"
}
}
}
},
{
"name": "OUT_PERIOD_COLUMN_AFTER",
"transform": [
{
"filter": "datum['_date_rank'] == -1"
}
],
"mark": {
"type": "bar",
"width": {"band": 0.95},
"tooltip": true,
"opacity": 0.8,
"stroke": "white",
"fill": {
"expr": "_out_colour"
}
},
"encoding": {
"y": {
"field": "_out_period_end_amount",
"type": "quantitative",
"axis": {
"title": "Amount"
}
}
}
},
{
"name": "OUT_PERIOD_LABEL_BEFORE",
"transform": [
{
"filter": "datum['_date_rank'] == -2"
},
{
"calculate": "format( datum['_out_period_start_amount'], '.3s' )",
"as": "_out_period_amount_label"
}
],
"mark": {
"type": "text",
"color": "black",
"align": "center",
"fontSize": 16,
"fontWeight": "bold",
"yOffset": 10,
"baseline": "top"
},
"encoding": {
"text": {
"field": "_out_period_amount_label"
},
"y": {
"field": "_out_period_start_amount",
"type": "quantitative"
}
}
},
{
"name": "OUT_PERIOD_LABEL_AFTER",
"transform": [
{
"filter": "datum['_date_rank'] == -1"
},
{
"calculate": "format( datum['_out_period_end_amount'], '.3s' )",
"as": "_out_period_amount_label"
}
],
"mark": {
"type": "text",
"color": "black",
"align": "center",
"fontSize": 16,
"fontWeight": "bold",
"yOffset": 10,
"baseline": "top"
},
"encoding": {
"text": {
"field": "_out_period_amount_label"
},
"y": {
"field": "_out_period_end_amount",
"type": "quantitative"
}
}
}
]
},
{
"name": "IN_PERIOD",
"transform": [
{
"filter": "datum['_date_rank'] > 0"
},
{
"calculate": "datum['_current_month_amount'] - datum['_previous_month_amount']",
"as": "_amount_variance"
},
{
"calculate": "datum['_amount_variance'] / datum['_previous_month_amount']",
"as": "_percent_amount_variance"
},
{
"calculate": "datum['_amount_variance'] < 0 ? datum['_current_month_amount'] : datum['_previous_month_amount']",
"as": "_amount_minimum"
},
{
"calculate": "datum['_amount_variance'] >= 0 ? datum['_current_month_amount'] : datum['_previous_month_amount']",
"as": "_amount_maximum"
},
{
"calculate": "format( datum['_amount_variance'], '+.2s' ) + '|' + format( datum['_percent_amount_variance'], '+.1%' )",
"as": "_in_period_amount_label"
}
],
"layer": [
{
"name": "IN_PERIOD_COLUMN",
"mark": {
"type": "bar",
"width": {"band": 0.95},
"opacity": 0.6,
"tooltip": true
},
"encoding": {
"y": {
"field": "_amount_minimum",
"type": "quantitative",
"axis": {
"title": "Amount",
"labelFont": "Segoe UI",
"labelFontSize": 14,
"titleFont": "Segoe UI",
"titleFontSize": 16
}
},
"y2": {
"field": "_amount_maximum"
},
"color": {
"condition": [
{
"test": "datum['_amount_variance'] < 0",
"value": {
"expr": "_in_colour_negative"
}
}
],
"value": {
"expr": "_in_colour_positive"
}
}
}
},
{
"name": "IN_PERIOD_POSITIVE_LABEL_BACKGROUND_RECTANGLE",
"transform": [
{
"filter": "datum['_amount_variance'] >= 0 && datum['_date_rank'] >= 1"
}
],
"mark": {
"type": "rect",
"width": {
"expr": "_in_period_background_rectangle_width"
},
"height": {
"expr": "_in_period_background_rectangle_height"
},
"color": "white",
"align": "center",
"yOffset": 0,
"baseline": "bottom"
},
"encoding": {
"y": {
"field": "_amount_maximum",
"type": "quantitative"
}
}
},
{
"name": "IN_PERIOD_NEGATIVE_LABEL_BACKGROUND_RECTANGLE",
"transform": [
{
"filter": "datum['_amount_variance'] < 0 && datum['_date_rank'] >= 1"
}
],
"mark": {
"type": "rect",
"width": {
"expr": "_in_period_background_rectangle_width"
},
"height": {
"expr": "_in_period_background_rectangle_height"
},
"color": "white",
"align": "center",
"yOffset": 2,
"baseline": "top"
},
"encoding": {
"y": {
"field": "_amount_minimum",
"type": "quantitative"
}
}
},
{
"name": "IN_PERIOD_POSITIVE_LABEL",
"transform": [
{
"filter": "datum['_amount_variance'] >= 0 && datum['_date_rank'] >= 1"
}
],
"mark": {
"type": "text",
"color": "black",
"align": "center",
"yOffset": -14,
"baseline": "bottom"
},
"encoding": {
"text": {
"field": "_in_period_amount_label",
"type": "nominal"
},
"y": {
"field": "_amount_maximum",
"type": "quantitative"
}
}
},
{
"name": "IN_PERIOD_NEGATIVE_LABEL",
"transform": [
{
"filter": "datum['_amount_variance'] < 0 && datum['_date_rank'] >= 1"
}
],
"mark": {
"type": "text",
"color": "black",
"align": "center",
"yOffset": 4,
"baseline": "top"
},
"encoding": {
"text": {
"field": "_in_period_amount_label",
"type": "nominal"
},
"y": {
"field": "_amount_minimum",
"type": "quantitative"
}
}
}
]
}
]
}
The intent of this example is not to provide a finished visual, but rather to serve as a starting point for further custom visual development.
Also included is the development sample PBIX using data sourced from the Microsoft AdventureWorksDW2019 sample database with dates adjusted forward by 10 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 Examples - Waterfall Chart (alternate).pbix (1.6 MB)