Power BI & DAX: How to Compare Timeseries with Different Start Dates

Let’s say you want to compare time series of two product launches that are six months apart. Plotting data chronologically will not be very user friendly. The best way to do it is to plot them based on number of days from the launch date. This way we can easily see which product grew to $1m in revenue faster than the other one. Similarly, as we analyze COVID data, we need to be able to compare multiple countries together even though some countries got their first cases much earlier than others.

If this chart looks interesting to you and you would like to learn how to make it, please see the video below:

This is the DAX formula for the calculation:

Normalized Deaths per 1M =

var dateReached = CALCULATE(MIN(‘Cases'[Date]),

‘Cases'[Daily Value]>=30,

KEEPFILTERS(‘Cases'[Metric] = “Confirmed”)

)

var MaxDate = CALCULATE(max(‘Cases'[Date]),

KEEPFILTERS(‘Cases'[Metric] = “Confirmed”)

)

var dayNo = SELECTEDVALUE(‘Days from 30 cases'[Day])

return CALCULATE([Deaths per 1M],

FILTER(ALL(‘Date’),

‘Date'[Date] = dateReached+dayNo &&

dateReached+dayNo<=MaxDate)

)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s