cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davemcnab Frequent Visitor
Frequent Visitor

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

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.

 

daysinmonths.PNG

 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

 

12 REPLIES 12
v-huizhn-msft Super Contributor
Super Contributor

Re: Annualize Ratios Dynamic (Year Quarter Month on annual basis with drill down) using DAX in Powe

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 Frequent Visitor
Frequent Visitor

Re: Annualize Ratios Dynamic (Year Quarter Month on annual basis with drill down) using DAX in Powe

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

 

dates.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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%

formula.PNG

 

 

 

 

 

 

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 Frequent Visitor
Frequent Visitor

Re: Annualize Ratios Dynamic (Year Quarter Month on annual basis with drill down) using DAX in Powe

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

 

dates.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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%

formula.PNG

 

 

 

 

 

 

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

Highlighted
intrasight Member
Member

Re: Annualize Ratios Dynamic (Year Quarter Month on annual basis with drill down) using DAX in Powe

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

davemcnab Frequent Visitor
Frequent Visitor

Re: Annualize Ratios Dynamic (Year Quarter Month on annual basis with drill down) using DAX in Powe

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.

Super User
Super User

Re: Annualize Ratios Dynamic (Year Quarter Month on annual basis with drill down) using DAX in Powe

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

 

 

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
davemcnab Frequent Visitor
Frequent Visitor

Re: Annualize Ratios Dynamic (Year Quarter Month on annual basis with drill down) using DAX in Powe

Hi CheenuSing and thank you for responding !

 

Unfortunately I think the solution is unrelated to the problem. Smiley Sad

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. 

Super User
Super User

Re: Annualize Ratios Dynamic (Year Quarter Month on annual basis with drill down) using DAX in Powe

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.

Capture.GIF

 

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

 

Cheers

 

CheenuSIng

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
davemcnab Frequent Visitor
Frequent Visitor

Re: Annualize Ratios Dynamic (Year Quarter Month on annual basis with drill down) using DAX in Powe

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.