In this article I show methods to calculate the doubling time with DAX in Power BI. Doubling time is an indicator used for exponential growth scenarios. It indicates how much time it takes for a figure to double.
You might have come across it in studies covering the current COVID-19 epidemy like here for example. In there you see how many days it took for cases to double. But these figures are shown as snapshot of today and I think it’s also helpful to see their development over time. With a bit of DAX we’ll get there:
Low values mean high speed of growth, so the bottom area is the danger zone here. I find that a bit unusual and thought about displaying it the other way around with negative numbers instead: Read more
There are many good reasons not to use Pivot charts as described in this article i.e. – one I would like to add is the bug when using “invert if negative”: The inverted colour will disappear once you refresh your data.
If you use Power Query to create the pivot for your chart, a regular chart will be created on it: Gone are the restrictions that come with the Pivot-Chart and it will also adjust dynamically to new or deleted rows and/or columns.
How to create your regular charts on dynamic pivot tables:
Cascading Time Granularities
When the client is not happy with the pivot report layout options on cubes in Excel, my usual reaction is “OK no problem, then we’re going to use cubefunctions instead”. This went well until recently: My client told me that he wanted a report like above: Years totals first, followed by quarters & months totals, but with growing number of years! All nicely close together, no gaps. Normally I don’t mind cubereports with dynamic table length (will blog about this later), but this is basically 3 dynamic reports under each other (yes they are still alive and will probably stay 😉 ):
So it was time to test what Chris Webb has talked about in this blogpost: Using Power Query as a report authoring tool in Excel. The idea was to use Power Query’s append-function to attaching 3 different report: Year, Quarter, Month. They could then have different lengths, the append-operation would seamlessly stitch them together. The connection to the cubedata was easy enough, thanks to his brilliant step-by-step guide. But then there were some challenges to solve:
How to create a Waterfall Chart using Power Query?
Target is a chart like above: Starting point, several movements & then an ending sum. Every movement starts at the end of the previous movement, upsides in green colour, downsides in red.
The source data for this kind of charts are normally missing the last line like this example with a P&L:
So this makes it always a bit clumsy to integrate in an automatically refreshing reporting package, especially if the number of lines/bars to report is dynamic.
So here comes the Power Query solution: Fully automatic & dynamic – calculation & charting.
Simply update your source data (Table: “Sourcedata”) & the rest will be done automatically. In this simple example the order of the Sourcedata-table will be kept in the chart.
Refresh the query or check “Data – refresh all” – done.
You can use this as it is – just prepare your table accordingly (fill in your numbers manually, create links or a power query to your source data… however), no need to adjust the query or even have a look at it.
But if you’re interested in how this works, just read on 🙂