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:
Doubling time in DAX (using small multiple visual from Daniel Marsh-Patrick)
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:
Charting doubling times as negative figures
But not convinced by that either. So I familiarized with the original display and read it: “Must get off the ground as quick as possible.”
The code for doubling times with DAX
Fortunately we can use variables to split the task into digestible steps:
Get currently evaluated date
This returns the latest day within the evaluated field.
Which value was equal or less than half of the current value
This gets the maximum value from the column with confirmed values at the currently evaluated date and halfs it. That’s the value we have to search for in the next steps and find out at which day that happens. (Please note that this is a shortcut and will only return correct results where figures are not decreasing.)
When did that threshold value occur?
This formula creates a virtual table with all dates with values (line 17) in them and adds a column with the sum of the daily amounts to it (line 16 and 18). Then it filters that table so that only amounts remain that are equal or below the threshold value (line 15 and 20). Remember: That’s the value where there were half as much instances than at the current date. From that filtered table: Grab the date’s maximum (line 14).
So now we know when there was half as much as the current (-ly evaluated) date we just have to calculate the difference to the current date like so:
Get the difference for doubling time in DAX
Finally a bit of cleanup to eliminate meaningless numbers and we’re done:
Cleanup to focus on meaningful numbers
Please note: This formula will work well with aggregations over time. So if you aggregate on week on month-level for example. There it will show the latest value of the time interval. But if you want to aggregate on a different level (like geography) for example, you will have to change the aggregation formula in the threshold variable from MAX to SUM instead (thanks to Vlad to pointing this out in the comments):
VAR ThresholdValue =
CALCULATE( SUM (COVID[Value]), COVID[Date] = CurrentDay) / 2
And here is the full code can be copied as well:
Remain healthy and stay queryious 😉
The post Calculating doubling times with DAX in Power BI appeared first on The BIccountant.