Calculate OPEN CASES over time in Power BI | Part 2

In Part 2 of How to Calculate Active Items over time I focus on how you can only include cases that are open for a certain time period. For example only employees who have been at the company for less than 1 year.
This is basically a cohort analysis with cohorts for 1, 2, 3 year and calculating active items respectively such as active employees, active subscriptions or open contracts.
Join me to learn how to calculate open cases over time in Power BI- a universal concept that is applicable to very many business cases.
Enjoy!
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly [e-mail]
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
0:00 Intro
0:59 Recap: Calculating the number of active employees
3:35 Include only active items that are open for a certain time period
5:26 Creating the dax measures for 1, 2, 3 year active employees
7:44 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO​
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on KZbin: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI​ #PowerBI​ #DataTraining​
#powerbidesktop​ #powerbitraining​ #powerbideveloper​ #DAX

[@HowtoPowerBI]

if you try it let me know!

[@xodrinker]

Perfect, have an actual case I need this in - thanks!

[@ballofwax2586]

great!!! very helpful... thanks so much. You do a great job at explaining how everything works and why.... easily one of my new favorite channels to learn with.. I am using these two videos you made to show active subscriptions.. My next feat is to figure out the dates on which these users did not renew their subscription... or in the case of active employees.... the dates at which employees left, to see if there is a pattern.

[@yadukrishnankv5265]

Hi Bas, awesome video! I've got a question though. If I'm trying to forecast the number of employees for the next few years (I have their planned leaving date) what edits should I do to the DAX? With the CurrentDate as in the video, it is calculating only till today(as I have my last day in my calendar table set to today). But when I changed last day to dec 31 2040, the DAX is not working as expected.

[@arvgdv]

this was very helpful!

[@HowtoPowerBI]

Happy to hear this!

[@agelikima4069]

Simply explained and great job. Uppon this example would there be any way to calculate the percentile of days of open cases over time? thank you in advance

[@HowtoPowerBI]

Pattern would be the same. You would need to exchange the employee count measure with the measure you want

[@agelikima4069]

@@HowtoPowerBI thank you very much :)

[@angelgutierrez2781]

Awesome work!!!!! Would you be able to to average? Like in May there where 10 active and 5 where above 1 year. Average for may over 1 year is 50%

[@MrScotee]

Great explanation! What if i wanted to sum or avergae the total tenure of employees at each point in time? Is this possible?

[@jacodj5413]

Hi Bas, Can this technique be adapted in some way to allow viewing the records (I.e. employee names) which make up the count? Thanks!

[@charlieschmidt6478]

This worked great but I am having trouble with getting slicers to interact with the visual correctly. Slicer/filter selected option does not affect the open case/active employee count. Any tips would be appreciated!

[@josefinagaccio2362]

Hi! If I need for example to calculate the same but only for working days, how can I estimate that? For example open tickets for more than 30 working days

[@lijunchen]

Hello Bas, This is a great tutorial. I have racked my brains to figure out how this can be done with no success. This is the only solution I have found on youtube or elsewhere to the case for historical count of open cases by dynamically calculated tenure or age groups. But I do have a question or suggestion. I wonder instead of creating a measure for every tenure group, is it possible to create a measure for the tenure or age group and use it as a legend to create the chart of the number of active open cases across time by the dynamic age or tenure group. This will help a lot if there are more than one KPIs (active employees, their wages, sales, workload, etc) that will be displayed in the same chart using the field parameters.

[@daveschorah9174]

Fantastic; adding time intelligence to my employee tracker has been the last thing that I had to accomplish and I've been tearing my hair out trying to work it out - thank you! The only thing I can't do is have the chart laid out by Qtr then year like in yours - I can only get the years 2014-2021 in the chart despite having date hierarchy in the axis field...?

[@WesselTerpstra]

A bit late, but you have to turn off concatenate labels and drill all the way down. I assume that that's what you meant

[@lalitkaushik1812]

Pls share video on open tickets and close tickets on weekly basis in IT dept.

[@conradoiiaustria5277]

Hey men, could you do a video if there is a rehire date on the same row of data

[@HowtoPowerBI]

wouldn't that be kinda the same thing,.. just extend the logic for the filter conditions?

[@jaggyjut]

I have helpdesk data and trying to build a visual to show the open and close tickets in a given period like day, month or quarter. Im not sure how to count a ticket that was open and closed in the same day?

[@celsodemetriojusto]

Did you ever find out how to do this?

[@jaggyjut]

@@celsodemetriojusto not yet in powerbi.