Community Summit Europe | March 9-12, 2020 | Barcelona, Spain Register today
View My Drafts
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.
Data Architect/ETL Developer
THE ART OF BRINGING STORIES TO LIFE.
825 Girod Street
New Orleans, LA 70113
OFFICE (504) 252-4500
DIRECT (504) 826-9080
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
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.
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?
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.