Calculate OPEN CASES over time in Power BI | Part 1

Подписаться 220 тыс.

Просмотров 23 тыс.

50% 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 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 RU-vid: 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

Наука

Опубликовано:

11 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 55

@hhFaktor 2 года назад

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

@katiewilliams5559 2 года назад

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.

@khardu 3 года назад

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

@HowtoPowerBI 3 года назад

awesome, thank you!

2 года назад

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

@HowtoPowerBI 2 года назад

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

@SolidSnake59 2 года назад

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 !!!!

@robertstewart614 Год назад

Brilliant and clear, many thanks

@pgplanas Год назад

Truly a life saver. Thank you!

@HowtoPowerBI Год назад

Happy to hear this Pablo!

@jakoblauridsen4347 2 года назад

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

@mariarazdelkina7920 2 года назад

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

@HowtoPowerBI 2 года назад

great to hear it helped Maria! 😊

@rschloch Год назад

Very helpful. Already subscribed. Thanks again!!

@HowtoPowerBI Год назад

Awesome:)

@FernandoRamirez-ce2ou 2 года назад

Thx Bass Another problem solved with this tutorial

@williamarthur4801 6 месяцев назад

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.

@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!

@TheGabrielbms Год назад

god bless you, you saved my life

@HowtoPowerBI Год назад

Oh that’s cool! 😎

@ProjectSibanda 2 года назад

Very helpful video brother!!

@HowtoPowerBI 2 года назад

Thx!

@nawafsharaf Год назад

Thank you ! You saved me 😍

@HowtoPowerBI Год назад

Glad to hear that :)

@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+

@FernandoRamirez-ce2ou 2 года назад

Great video

@conradoiiaustria5277 3 года назад

Subscribed!!

@jasonwoodward5501 2 года назад

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

@liweiyang7151 2 года назад

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?

@wilks37 2 года назад

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.

@sandhyamanjunath 2 года назад

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

@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 ?

@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?

@ballofwax2586 3 года назад

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 3 года назад

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

@HowtoPowerBI 3 года назад

there we go! ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-7_kAJ7wSwJk.html :)

@amydavies2213 8 месяцев назад

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

@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?

@EvilGost7878 2 года назад

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 2 года назад

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

@ridohconcepts8868 3 года назад

Great video!, kindly share the file for practice.

@HowtoPowerBI 3 года назад

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

@DanielTavares29 3 года назад

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 3 года назад

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

@DanielTavares29 3 года назад

@@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

@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?

@markherman9561 3 года назад

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 3 года назад

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

@HowtoPowerBI 2 года назад

Please have a look in the video description :)

@sukoi2113 3 года назад

Thanks again, your solutions are so smart. Sexy!

@dianarebecca3473 2 года назад

what is dimDate? I did not understand.

@HowtoPowerBI 2 года назад

Hi Diana :) that's the name of the date dimension table. This video explains all about it -> ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-MhC4zj2byBQ.html