Calculate OPEN CASES over time in Power BI | Part 1
Vložit
- čas přidán 31. 07. 2024
- 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 support@datatraining.io
---------------------------------
⏱️ 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 CZcams: 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 - Věda a technologie
Just what I was looking for, and using variables makes the code much cleaner. Thank you.
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.
Love your explanation and pace. You got a new subscriber!
Thank you.
awesome, thank you!
Thank you very much, I was struggling for last two weeks to implement one scenario similar to this, and finally ALL() works like a magic. Your detailed explanation is much helpful.
Although data are matching now but unfortunately facing poor performance. Note we have three dates in fact with which we need to apply filter conditions similar to what you explained and also we have direct query mode, not sure if the performance can be improved ☹️
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 !!!!
Your step by step explanation are really powerful. Also like the emphasis on the context and need to use ALL()
that's very nice to hear, thx François 🙂
Brilliant and clear, many thanks
You have just done 1/3 of my dashboard thank you!
Truly a life saver. Thank you!
Happy to hear this Pablo!
That was awesome, just was I was looking for this week to find number of active student at a college month over month
Very helpful. Already subscribed. Thanks again!!
Awesome:)
Thank you so much! This video helped me a lot!
great to hear it helped Maria! 😊
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.
Thx Bass Another problem solved with this tutorial
Subscribed!!
Great video
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!
Very helpful video brother!!
Thx!
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+
Thank you ! You saved me 😍
Glad to hear that :)
god bless you, you saved my life
Oh that’s cool! 😎
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?
This is brilliant. What about materialization cost for large tables?
This was very helpful. Can you please let to know how I can get to know the employee number that are cumulatively added
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 ?
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.
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
This is very useful. How might I be able to enter any date and show the figure at that date please?
Does this not work with fields that have a time stamp as well?
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.
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?
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 ?
Hello!! Did you find a solution for your problem? I have the same issue! The All suppresses the date slicer selection.
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?
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?
ah interesting, I will create a video on how to do that and upload it coming Tuesday
there we go! czcams.com/video/7_kAJ7wSwJk/video.html :)
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?!
Not sure, I haven't seen it yet. From the title it sounds like it covers the same type of calculation.
@@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
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
kindly share .pbix file for practise ...it would be a great help for your active subscribers
Please have a look in the video description :)
Great video!, kindly share the file for practice.
thx Ridoh! datatraining.io/youtube/powerbi/040_active_items/active_items.zip
Thanks again, your solutions are so smart. Sexy!
what is dimDate? I did not understand.
Hi Diana :) that's the name of the date dimension table. This video explains all about it -> czcams.com/video/MhC4zj2byBQ/video.html