Annualize Ratios Dynamic (Year Quarter Month on annual basis with drill down) using DAX in Power BI

[davemcnab]

A basic need for many clients is to compute an annualized attrition rate. The baseline formula  - assuming we're talking client attrition - would be:

Annualized attrition rate % =  (count of lost clients in period * 100 * (days in year / days in period)) / (number of clients a period start)

The (100* (days in year / days in period)) component is called a Period Annualization Factor or PAF

This is a no brainer if you just want a monthly rate. In fact using "12" as a proxy for (days in year / days) in period is a decent proxy i.e. it computes out pretty closely - within 2%. But that is not accurate enough for our client.

 Even more importantly this math does not enable proper drill down (or up) on the Rate by Year, Quarter, or Week for example. To do that we need to have dynamic count of days in the period selected (in our case using a date slicer). So when we build a report that is based on a time series, and we want to have different levels of categoric date summary for a calculated rate, we are finding no joy.

Already attempted:

Use FIRSTDATE and LASTDATE with YEARFRAC as:

•  DateRangeFirst = FIRSTDATE(Datatable[EffectiveDate].[Date])
•  DateRangeLast = LASTDATE(Datatable[EffectiveDate].[Date])
• DatePAF = 100/(YEARFRAC([DateRangeFirst],[DateRangeLast],3))

Anyone know how to make the count of number of days in a selected date range dynamic?

I expect someone has figured this out already, but I have googled, searched the forums and spent more than a day researching this in DAX examples with no joy. Hopefully when we figure it out (with your kind help?!?!?) it will benefit many others.

Thanks in advance for your thoughts, ideas and advice !

- Dave

Also, and more importantly

[intrasight]

I have the same question and so am hoping someone has figured out how to express this in DAX or some other means

[davemcnab]

I feel your pain. We are using this for banks... annualizing ratios on days in the month are kind of important to get right!

Let's keep hoping we get some love from the community.... I expect someone is clever enough to have solved this already but it does not seem to be anywhere I search.

[CheenuSing]

Hi @davemcnab, @intrasight

Get Set and GO !!!!!!!!!!

I managed to get a solution for the problem posed.

Here is the one drive link to the pbix.

https://1drv.ms/u/s!ApP3mBZyGaHfgRsxqhfnwM5_8Ndc

Here is the one drive link to the steps followed

https://1drv.ms/f/s!ApP3mBZyGaHfgR4ZJOsePW5Pban6

Happy days are ahead.

If this resolves your problem, please accept it as a solution and also give KUDOS.  

Cheers

CheenuSing

[davemcnab]

Hi CheenuSing and thank you for responding !

Unfortunately I think the solution is unrelated to the problem.

I studied the "steps" document and it appears to be talking about "peak periods" of sales which is not what this is about. Also there are things about product launched = no and assumption about a Year being assumed as always selected... so yabe it is just a post in the wrong thread.

PS the .pbix file in the one drive cannot be opened in Power BI when unzipped. Not sure what the files are, but since they are off topic this is just a call out that you might want to zip the actual pbix file so it is useful.

So, we're still stuck and hoping someone has solved this and can share !

Thanks again CheenuSing, it was good of you to try to help. 

[CheenuSing]

Hi @davemcnab

So sorry that a wrong file got uploaded. I am sendding the link for the steps file

https://1drv.ms/w/s!ApP3mBZyGaHfgRyIMyDUrQQ4zFCx

The link for the pbix file

https://1drv.ms/u/s!ApP3mBZyGaHfgRsxqhfnwM5_8Ndc

A screen shot of the ouput created.

If the downloads still donot work, share me your email id through the private message and I will email the files.

Cheers

CheenuSIng

[Arjun_SP]

@CheenuSing

sparjun6@gmail.com ,

Can you please send me the files. . .

Thanks much. . !!!

[davemcnab]

Thanks for revisiting this @CheenuSing . You are obviously a helpful (and skilled) person!

On review of your approach I think it might work. We'll try to interpret the approach into our model and update when we know the outcome. One thing that struck me was the use of columns seems unnecessary: I think we can apply the logic using just measures which would save a lot of space (we are running up to 8 million rows in our fact data). so I may get back with a slightly modded expression... but I will get back to you and credit you with the solution if our testing proves it out !

Thanks again for being supportive. Really appreciative.

[Cubist]

Did everyone on the thread get this figured out to their satisfaction?

If @CheenuSing' answer was the ticket then great, @davemcnab can you please mark his answer as the solution?

If not, please advise because I just spent a fair amount of time solving this and can post more detail if it might help.

Thanks 

[davemcnab]

Thanks for your post. In fact the solution proposed works (I think.. did try but did not get to end of job) but it requires creation of additional columns and I need to work it with only calculated measures instead due to volume considerations. Our data volume is millions of records so adding columns is not a practical way to go. 

What I was hoping for is a simple and space efficient calculation that would yield the period adjustment factor for each period in a time series report, so annualized trends could be displayed precisely and simply. If you have thoughts on that please do share them !

[v-huizhn-msft]

Hi @davemcnab,

First, how do you get the results highlighted in yellow?

>> Even more importantly this math does not enable proper drill down (or up) on the Rate by Year, Quarter, or Week for example. To do that we need to have dynamic count of days in the period selected (in our case using a date slicer).

What do you mean of “this math does not enable proper drill down (or up) on the Rate by Year, Quarter, or Week for example” ? For drilling down, you should create a hierarchy. Based on my understanding, you want create a timeline slicer including date, when you select period in slicer, you need to create the count of days, right? If it is, you can use the following formula.

Measure 2 = DATEDIFF(MIN(DateTable [Date]),MAX(DateTable[Date]),DAY)

If this is not what you want, please describe your issue clearly, and share more details or sample date for further analysis. Thanks a lot.

Best Regards,
Angelia

[davemcnab]

Thanks so much for offering this answer Angelia. I don't think it solves the problem (yes i tried it) so an elaboration is warranted.

Also when I tried the formula suggested the days counts for months etc. appear inaccurate - one day short every period.

I believe the correct formula requires a "+1" to be correct.

Measure 2 = DATEDIFF(MIN(Calendar[DateKey]),MAX(Calendar[DateKey]),DAY)+1

First the "yellow" values in original post. This is a compare of the ratios of days in month/365 to 12/365 and is the %difference between the two.

Now on to the more interesting bits.

We are using a date slicer to select date range for the Tile in question.

The tile includes a date hierarchy of Y, Q and M.

The calculation (detailed in initial post) is date period sensitive i.e. annualizing monthly data requires that the numerator be multiplied by a factor to obtain an annual equivalent which is divided by the denominator (which is a period value, but represents an complete population.

An example:

Say we have 1000 customers at the beginning of February and 10 leave us.

The raw monthly rate of  10/1000 is 0.1%.

To annualize the monthly rate we do this: 10*(365days/28days) / 10000 = 1.3036%

On a quarterly basis we'd sum the numerator values = lost customers each month, divide by 365/sum of days in quarter and divide by the denominator value = number of customers at beginning of the quarter.

Annually it would be ..... for the year .... beginning of the year.

Maybe it isn't the date range, that's causing grief, but the selection of the "number of customers at beginning of" ? since this is also date range sensitive.

It seems impossible to do this in a single tile.  If anyone knows how to make it work right please let us know !

Thank you

[davemcnab]

Thanks so much for offering this answer Angelia. I don't think it solves the problem (yes i tried it) so an elaboration is warranted.

Also when I tried the formula suggested the days counts for months etc. appear inaccurate - one day short every period.

I believe the correct formula requires a "+1" to be correct.

Measure 2 = DATEDIFF(MIN(Calendar[DateKey]),MAX(Calendar[DateKey]),DAY)+1

First the "yellow" values in original post. This is a compare of the ratios of days in month/365 to 12/365 and is the %difference between the two.

Now on to the more interesting bits.

We are using a date slicer to select date range for the Tile in question.

The tile includes a date hierarchy of Y, Q and M.

The calculation (detailed in initial post) is date period sensitive i.e. annualizing monthly data requires that the numerator be multiplied by a factor to obtain an annual equivalent which is divided by the denominator (which is a period value, but represents an complete population.

An example:

Say we have 1000 customers at the beginning of February and 10 leave us.

The raw monthly rate of  10/1000 is 0.1%.

To annualize the monthly rate we do this: 10*(365days/28days) / 10000 = 1.3036%

On a quarterly basis we'd sum the numerator values = lost customers each month, divide by 365/sum of days in quarter and divide by the denominator value = number of customers at beginning of the quarter.

Annually it would be ..... for the year .... beginning of the year.

Maybe it isn't the date range, that's causing grief, but the selection of the "number of customers at beginning of" ? since this is also date range sensitive.

It seems impossible to do this in a single tile.  If anyone knows how to make it work right please let us know !

Thank you