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:

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:

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.)

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:

Finally a bit of cleanup to eliminate meaningless numbers and we’re done:

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:

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.

Learn more about bidirectional Unicode characters

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 |

Remain healthy and stay queryious 😉

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

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

Hi where do i enter the DAX code – as a new measure or column?

It’s supposed to be used as a measure.

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

Sorry, but it make take some time for me to catch this up again.

Can you sum confirmed numbers, are these number additive?

SUM ( DailyData[Confirmed] )

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.

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

Sounds good!

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?