Calculate OPEN CASES over time in Power BI | Part 1

In this video I focus on calculating active items over time. This can be used when you only need to know the active employees, active subscriptions or open contracts.
You will learn how to create a measure in DAX and in turn calculate only active cases over time.
Enjoy!
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 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:28 Setting the base for the calculation
2:08 Calculating the number of active employees
5:38 Limiting the time frame
6:45 Using the calculation
07:18 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

[@hhFaktor]

Just what I was looking for, and using variables makes the code much cleaner. Thank you.

[@katiewilliams5559]

Very useful, thank you. I made some adjustments and used it to calculate open invoices (entered, not paid) for any end of month in the data history.

Your step by step explanation are really powerful. Also like the emphasis on the context and need to use ALL()

[@HowtoPowerBI]

that's very nice to hear, thx François 🙂

[@khardu]

Love your explanation and pace. You got a new subscriber! Thank you.

[@HowtoPowerBI]

awesome, thank you!

[@robertstewart614]

Brilliant and clear, many thanks

[@SolidSnake59]

You have just done 1/3 of my dashboard thank you!

[@GeraldHilbers]

Great explanation again. I like the way you show the steps you take to come to the final solution. Especially for this for me hard to understand DAX-language: VARs which aren't vars, FILTER context, use of CALCULATE and not use FILTER within CALCULATE. Still have a lot of probs with DAX, so any concrete stepwise solution is helpful. Bedankt !!!!

[@jakoblauridsen4347]

That was awesome, just was I was looking for this week to find number of active student at a college month over month

[@Woodborn]

I was struggling with code to do this from another source and it kept screwing with my filters. Your solution solved my problem and is very clean. Thanks!

[@williamarthur4801]

I was trying various options, things like lastnonblank etc. another good video, i still have to check my results. mocked up small data set to practice on.

[@rschloch]

Very helpful. Already subscribed. Thanks again!!

[@HowtoPowerBI]

Awesome:)

[@pgplanas]

Truly a life saver. Thank you!

[@HowtoPowerBI]

Happy to hear this Pablo!

[@mariarazdelkina7920]

Thank you so much! This video helped me a lot!

[@HowtoPowerBI]

great to hear it helped Maria! 😊

[@ClaudiaMorales-mw7is]

Great explanation. I was able to modify for my needs. Thank you!. I am now trying to figure out a breakdown by an aging category. With this dataset I need to know for any given month, how many employees had less than 3 years of senority, how many 3-6, how many 6-10 and how many 10+. My dataset is open orders so I need to calculate for any given month how many open orders were less than 30 days old, how many 31-60, 61-180, 180+

[@jasonwoodward5501]

This is brilliant. What about materialization cost for large tables?

[@liweiyang7151]

Thank you so much, this really helped me with distinctcount active accounts and closed accounts with open contracts dates and closed contracts dates. I did some editing. However, when I use the totalytd, it is not working. I’m wondering why?

[@FernandoRamirez-ce2ou]

Thx Bass Another problem solved with this tutorial

[@AnisurRahman-wm2ys]

Hello, thanks for your excellent work. I have one question, what we need to change in the measure if we want to get open case by department over time ? have you this type of video ?

[@sandhyamanjunath]

This was very helpful. Can you please let to know how I can get to know the employee number that are cumulatively added

[@wilks37]

Really helpful, anyway you could show this for time of day, for example how many open between 6am and 3pm? Also for events open that's stay open passed midnight.

[@conradoiiaustria5277]

Subscribed!!

[@FernandoRamirez-ce2ou]

Great video

[@ProjectSibanda]

Very helpful video brother!!

[@HowtoPowerBI]

Thx!

[@newtonbase]

This is very useful. How might I be able to enter any date and show the figure at that date please?

[@aashiijain]

Hi, This measure was very helpful. Thanks for uploading this video. Is there any way we can increase the performance of this measure because it usually takes 5 10 min to load the data. If you can help me with that it would be really helpful for me. Also, am i the only one facing performance issue with this?

[@nawafsharaf]

Thank you ! You saved me 😍

[@HowtoPowerBI]

Glad to hear that :)

[@TheGabrielbms]

god bless you, you saved my life

[@HowtoPowerBI]

Oh that’s cool! 😎

[@gmartinezm]

Bro this is great, I have a problem I want to create a bar char including Orders Created, and Orders Closed per week. But, I want to still show Orders Closed even if Orders were not created on that week, Thoughts?

[@ballofwax2586]

Great video. I am using your method to show active subscriptions on some products. What if I want to view 1year, 2year, and 3 year active subscriptions? What kind of variable can I add for this?

[@HowtoPowerBI]

ah interesting, I will create a video on how to do that and upload it coming Tuesday

[@HowtoPowerBI]

there we go! kzbin.info/www/bejne/bZDOcn1trLiqgM0 :)

[@amydavies2213]

Thank you for this. It works really well, but when I create a drill through from it, it doesn't return the correct records and returns records that have closed prior to the end date. Anyone know where I am going wrong? Thanks

[@EvilGost7878]

Hi ! Thank you so much, very useful! But I encounter an issue. If I put a date filter in the page, because I want to focus only on active employee for 2020 for example, powerbi show me a wrong active number because it does not count hiring before 2020, because it is filtered, despite the usage of All(DimDate). Do you know why ?

[@orlandoaguilar7786]

Hello!! Did you find a solution for your problem? I have the same issue! The All suppresses the date slicer selection.

[@MiguelJimenez-pg6zs]

In my case, I need to see the number of Active employees at the start of the month instead of the end of the month? I change the Var CurrentDate to Min but I am not getting the result I am looking for.

[@sofieakesson790]

Does this not work with fields that have a time stamp as well?

[@DanielTavares29]

This calculation definitely comes back over and over again hahahaha Is it the same as the one called "events on progress" by the SQLBI guys?!

[@HowtoPowerBI]

Not sure, I haven't seen it yet. From the title it sounds like it covers the same type of calculation.

[@DanielTavares29]

@@HowtoPowerBI besides that thanks for your video!!! It helped me a lot 👍👍👍 actually there is a small difference which the condition ISBLANK filtering the table is something that i haven't seen until now

[@ridohconcepts8868]

Great video!, kindly share the file for practice.

[@HowtoPowerBI]

thx Ridoh! datatraining.io/youtube/powerbi/040_active_items/active_items.zip

[@markherman9561]

I found this very fhelpful but for some reason I didn't count the dates in the dimension table, rather the columns in my source table

[@aas2943]

kindly share .pbix file for practise ...it would be a great help for your active subscribers

[@HowtoPowerBI]

Please have a look in the video description :)

[@sukoi2113]

Thanks again, your solutions are so smart. Sexy!

[@dianarebecca3473]

what is dimDate? I did not understand.

[@HowtoPowerBI]

Hi Diana :) that's the name of the date dimension table. This video explains all about it -> kzbin.info/www/bejne/g5mmZa2gZ8eseLM