• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate Trailing 12 Months - Power BI

M

msquared99

Member
Anyone have any ideas on how to calculate a trailing 12 months in Power BI to create a graph showing how the client base has changed over time during a 12 month period?

My calculation in Excel is Retention Rate = (End Bal. - New)/Begin Bal.

I have data where there is a start date and an end date. I want to do a rolling trailing 12 months calculation. So as an example, I would be looking at data from Dec-16 thru Nov-17.

Dec-16 would be counting all clients from Jan-16 thru Dec-16 and rolling forward for 12 months, then Jan-17 thru Dec-17, and Feb-17 thru Jan-18 and so on .

The end result would be:
Dec-16 88.9%
Jan-17 87.6%
Feb-17 90.1%
Mar-17 89.9%
And so on til...
Nov-17 88.6%
 
I'd suggest uploading sample data table (in form of Excel workbook). That way, we don't have to guess at your data structure and can give you solution that will fit your need.
 
Chihiro,

I have attached an example excel file. The Date Table will be a slicer to control the dates. I'll need to do the calculations in columns J thru M. Column M will be the measure that will be displayed in Power BI.

I am looking for a way to count between dates using DAX.
 

Attachments

  • Retention Rate.xlsx
    288.9 KB · Views: 6
Umm, is the data accurate? Does it really start from 1900 on start date? Or was that DB error?
 
Hmm, I got around above issue by creating date dimension table.

But I don't get your logic for Retention.
 
Yes the 1900 date is correct as some were entered that way into the DB.

For retention it would be (4,759 - 37 )/5,294 = 89.2%

My issue is how do I not count the one's that started in the period but also left within the period. I think it is going to take some tricky filtering.
 
Sorry for late reply. I've been on vacation for last week and a bit.

I am still unclear on your logic for retention calculation.
What's the logic used to decide the formula?
=L14/L3

Why the first retention is calculated from 9 month previous, but all others are done on monthly basis?
 
You must log in or register to reply here.
Back
Top