Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Retention Rates

  • 1.  Retention Rates

    Posted Jul 24, 2019 06:04 PM

    As with many community posters, I'm pretty new to Power BI and could use some pointers.

     

    I have a table of employees (id, name, department, hire_date, termination_date), and I'd like to calculate a per-department retention rate based on a user-defined time period (date_1 and date_2).  The output would just be a number on a card, like 84%.


    I've found many examples online, but none seem to be what I'm after.


    I know that Retention Rate = % of employees at date_1 that are still employed at date_2.  And I know that # of employees at date_1 is anyone with a hire_date prior to date_1, and a termination_date that is either blank or after date_1.

     

    So...

    • How does the user select the start/end time period (date_1 and date_2)?  Slicers?  Filters?  Other?
    • What changes to my data structure do I need to make (dates table(s)?  Departments table?)
    • What measures should I create to use the above dates?

     

    Thanks,

     

    Brent Paloutzian

    Data Architect/ETL Developer

     

    Solomongroup

    THE ART OF BRINGING STORIES TO LIFE.

    825 Girod Street

    New Orleans, LA 70113

    OFFICE (504) 252-4500

    DIRECT (504) 826-9080

     

    solomongroup.com



  • 2.  RE: Retention Rates

    Silver Contributor
    Posted Jul 25, 2019 07:56 AM
    I would start with creating a departments table that has count of employees at any given time. Either in power query or an ETL process outside of power bi and just import a new object into power bi. once you have that, you should be able to do your calculations. Then from a visualization point, you can use slicers to change your time periods.

    If you're wanting to calculate it from each date 1 compared to each date2, I think you'll have to do it outside PBI (cross join the table to itself in SSMS) and bring the results into pbi

    ------------------------------
    Sam Duval
    Data Quality Analyst
    Indianapolis
    ------------------------------



  • 3.  RE: Retention Rates

    Top Contributor
    Posted Jul 25, 2019 09:34 AM

    Hi @Brent Paloutzian


    I believe you are looking for a dynamic segmentation pattern: https://www.daxpatterns.com/dynamic-segmentation/

    Effectively, you would​ have a two slicers (one for Start Date, the other for End Date), and these inputs are what goes into the pattern described in the article.

    That is very high level, I know, but this should get you started in the right direction.

    Hope this helps!
    William



    ------------------------------
    William Rodriguez
    Business Analyst | MCSA: BI Reporting
    ------------------------------



  • 4.  RE: Retention Rates

    Bronze Contributor
    Posted Jul 25, 2019 02:15 PM
    Brent -
    See attached.  I dummied up some data to demonstrate the approach.  I haven't spent a ton of time on it so it's not completely vetted but this is one possible solution.
    The magic behind this is/are disconnected date tables.
    I created the date tables in DAX using the CALENDARAUTO() function.  Super easy way to quickly generate a date table.
    You can accomplish the user-selecting-a-date-range requirement either using two date tables (the "Two Slicers" page) or one date table using a Between Slicer (the "One Slicer" page).
    I also defined my "Active" employees as anyone hired both before the date range as well as hired within the date range - i.e. anyone hired before the End Date.
    I defined my "Termed" employees as anyone hired before the end date and termed within the date range.
    The Retention Rate is then the simple division Active - Termed / Active.

    Hope that helps.

    ------------------------------
    Eric Lofstrom
    Principal
    BlueGranite Inc.
    elofstrom@blue-granite.com
    ------------------------------

    Attachment(s)

    xlsx
    ActiveEmployees.xlsx   10K 1 version
    pbix
    RetentionRate.pbix   93K 1 version


  • 5.  RE: Retention Rates

    Posted Jul 25, 2019 04:12 PM

    Thanks Sam, William, and Eric for the different approaches.


    Eric, this is exactly what I was after!  Granted, I changed Active to "hired on or before date_1" (if they weren't an employee on Jan 1, 2019, we couldn't have "retained" them on March 31), but I now see how you made this work and was able to tweak it as needed.  You connected all of the dots for me.

     

    Much appreciated,

     

    Brent Paloutzian

    Data Architect/ETL Developer

     

    Solomongroup

    THE ART OF BRINGING STORIES TO LIFE.

    825 Girod Street

    New Orleans, LA 70113

    OFFICE (504) 252-4500

    DIRECT (504) 826-9080

     

    solomongroup.com

     






  • 6.  RE: Retention Rates

    Posted 29 days ago
    Thanks again to Eric for solving my previous problem so perfectly.  The sample PBIX file you provided was very similar to my data and your simple measures did the trick.  I'm back to ask the community for more assistance, expanding on this same topic.

    In addition to calculating a monthly retention rate for a given month, I've now been asked to chart (basic bar or line) the monthly retention rate for the past "few" months (3? 4? 6? it can be a fixed number) and I can't figure out where to begin.

    As a reminder, the key fields of my fact table of employees include Name, Hire_Date, and Termination_Date.  With a monthly date slicer on a calendar/date table, I was able to add the following measures: (psuedo code)

    Active on day 1 of my monthly range:
    Active = SumX(myTable, IF(myTable[Hire_Date] <= MIN('Date'[Date]) && myTable[Termination_Date] > MIN('Date'[Date]), 1, 0))

    Terminated within my 1 month range (and hired on or before day 1):
    Termed = SumX(myTable, IF(myTable[Hire_Date] <= MIN('Date'[Date]) && myTable[Terminate_Date] > MIN('Date'[Date]) && myTable[Termination_Date] < MAX('Date'[Date]), 1, 0))

    The monthly retention rate is easy, as it's just:
    DIVIDE([Active] - [Termed], [Active])


    But when it comes to charting multiple months, I'm lost.  I don't think I want individual monthly measures (like selected month -1, selected month -2, selected month -3, etc.), as these would be separate columns, which wouldn't work for a simple bar chart.  I feel like I need to calculate individual monthly retention rates, so that I can have this as the Value of my chart and have some dates as the Axis, but where are the dates coming from?

    Can anyone point me in the right direction?

    Thanks,



    ------------------------------
    Brent Paloutzian
    Data Architect
    New Orleans LA
    5048269080
    ------------------------------



  • 7.  RE: Retention Rates

    Gold Contributor
    Posted 28 days ago
    Hi @Brent Paloutzian,

    Just put Date[Date] on the axis and your Retention measure as the value and it should be fine as a bar or line chart.  You can add a slicer for Date[Date] to the page if you want to restrict it.

    Regards,​

    ------------------------------
    Simon Lamb
    IT Consultant, Method Excel Ltd.
    ------------------------------



  • 8.  RE: Retention Rates

    Posted 28 days ago
    @Simon Lamb,

    Thanks!  It occurred to me last night that I was over thinking it and trying to do all the work for Power BI instead of letting it do the work for me.  It's certainly a paradigm shift.

    I appreciate the community's tolerance for even the simplest of questions.

    ------------------------------
    Brent Paloutzian
    Data Architect
    New Orleans LA
    5048269080
    ------------------------------



  • 9.  RE: Retention Rates

    Posted 28 days ago

    So my issue was that I had a report-level filter on my Date/Calendar table limiting the dates to one month, which is what I wanted for most of my visuals.  I couldn't get the chart of monthly retention rates because of this filter and was going about it the (very) wrong way.  After changing my report-level filter to six months, then creating a new measure to pull the first day of last month in the range (combined with max date), I was able to rebuild my monthly measures.

    In other words, I was trying to filter everything very narrowly, then broaden my filter for a few visuals, when instead, I needed to filter broadly then create new measures to narrow a few visuals.

    The person using this report can now simply change 1 report-level filter to adjust the range and it impacts every page of the report, but also shows the monthly data for for the last month in the range.

    Hopefully understanding this approach helps someone else who, like me, is still learning.

    Thanks again for the help.



    ------------------------------
    Brent Paloutzian
    Data Architect
    New Orleans LA
    5048269080
    ------------------------------