Calculating doubling times with DAX in Power BI

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:


DoublingTime =
VAR CurrentDay =
MAX(DailyData[Date] )
// Get half of todays value
VAR ThresholdValue =
CALCULATE(MAX ( DailyData[Confirmed] ), DailyData[Date] = CurrentDay ) / 2
// Get the day when the number of cases was half as much as today
// FILTER returns dates at which the total sum of confirmed cases was below the threshold
VAR DayOfHalf =
CALCULATE (
MAX ( DailyData[Date] ),
FILTER (
ADDCOLUMNS (
SUMMARIZE ( ALL ( DailyData ), 'Date'[Date] ),
"Amount", CALCULATE ( SUM ( DailyData[Confirmed] ) )
),
[Amount] <= ThresholdValue
)
)
VAR DoublingTime = ( CurrentDay – DayOfHalf ) * 1
// Select only meaningful values
VAR Result =
IF ( AND ( DoublingTime < 10000, DoublingTime > 0), DoublingTime )
RETURN
Result
// For details see this blogpost: https://wp.me/p6lgsG-2g8

view raw

DoublingTime.dax

hosted with ❤ by GitHub

Remain healthy and stay queryious 😉

Comments (11) Write a comment

  1. Thanks. This was really useful. One note though, why use MAX instead of SUM in the Threshold variable?
    With MAX you cannot aggregate the measure to calculate the DoublingTime world wide and it would make sense if you have multiple values reported for the same ([Date],[Country]) tuple

    Reply

    • Hi Vlad,
      I used MAX because I can then use it on week or month-level.
      But yes, if you want to aggregate on different dimensions, one has to change that.
      Thanks for this suggestion.
      /Imke

      Reply

  3. Hello Sir,

    I in this case I observed that the doubling time considers the whole period but many of the analysis over the internet consider only the time since it was doubled such as they show it as “Increased by a factor of x no. of days”. For example – 57 Cases on March 22 and 122 Cases on April 19 and will be 2.4 instead of 2. The factor keeps on changing for various countries instead of keeping it as constant 2 (Double). I would love if you can help me out with the DAX so that I can also create an Analysis in Power BI. I tried the DAX which is there in this article but my analysis varies considerably which comparing it with the Internet due to factor kept as only 2 and also because of considering the whole time frame. Please help me out.

    Thanks & Regards

    Reply

    • Aggregations are problematic with this approach.
      For some the SUM aggregation would be appropriate and for others the MAX.
      You have to adjust to your needs.

      Reply

  5. Dear Imke
    Thanks for your post. I got a similar result to you but I solved it using a cross-joined table and assigning a score to each pair of dates. Unfortunately I have not been able to create a measure since it seems that DAX does not allow me to access the columns of virtual tables.
    Best,
    Diego

    Reply

  6. Hi, using Max, I had no result (my value was a record counter, not a daily count). With Sum, I end up with a value of 3. PLotting this on a graph using Date as the X axis, I do get a result that seems valid.

    However, what would be the unit for 3. Ie doubling time of 3 months?

    Reply

Leave a Reply