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 ☹️
Hey man, just discovered your channel and it's really boosted my abilities in PowerBI (and quickly!). I have a question for this measure, you reset the filter with ALL, but how would you then go about applying slicers to the result? For instance, if you had different departments that you wanted to calculate and show separate (or all together) with the use of a slicer. Currently that isn't working for me, I guess because of the ALL() line in the measure? EDIT: I found the solution to my problem. To avoid ALL() clearing the filters you might want to use, I instead used ALLSELECTED() and that applied my slicers to these measures as well. Thank you once again for your channel!
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.
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+
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
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?
@@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
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 ?
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 !!!!
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.
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!
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?
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 ?
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?
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?
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.
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 ☹️
Just what I was looking for, and using variables makes the code much cleaner. Thank you.
Love your explanation and pace. You got a new subscriber!
Thank you.
awesome, thank you!
Hey man, just discovered your channel and it's really boosted my abilities in PowerBI (and quickly!). I have a question for this measure, you reset the filter with ALL, but how would you then go about applying slicers to the result? For instance, if you had different departments that you wanted to calculate and show separate (or all together) with the use of a slicer. Currently that isn't working for me, I guess because of the ALL() line in the measure?
EDIT: I found the solution to my problem. To avoid ALL() clearing the filters you might want to use, I instead used ALLSELECTED() and that applied my slicers to these measures as well. Thank you once again for your channel!
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.
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+
You have just done 1/3 of my dashboard thank you!
Truly a life saver. Thank you!
Happy to hear this Pablo!
god bless you, you saved my life
Oh that’s cool! 😎
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.
Very helpful. Already subscribed. Thanks again!!
Awesome:)
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 was very helpful. Can you please let to know how I can get to know the employee number that are cumulatively added
Very helpful video brother!!
Thx!
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?
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
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
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.
Subscribed!!
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
what is dimDate? I did not understand.
Hi Diana :) that's the name of the date dimension table. This video explains all about it -> th-cam.com/video/MhC4zj2byBQ/w-d-xo.html
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 🙂
Thanks again, your solutions are so smart. Sexy!
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 !!!!
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.
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!
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?
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 ?
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?
Thx Bass Another problem solved with this tutorial
That was awesome, just was I was looking for this week to find number of active student at a college month over month
This is very useful. How might I be able to enter any date and show the figure at that date please?
This is brilliant. What about materialization cost for large tables?
Does this not work with fields that have a time stamp as well?
Brilliant and clear, many thanks
Great video!, kindly share the file for practice.
thx Ridoh! datatraining.io/youtube/powerbi/040_active_items/active_items.zip
Thank you so much! This video helped me a lot!
great to hear it helped Maria! 😊
Thank you ! You saved me 😍
Glad to hear that :)
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! th-cam.com/video/7_kAJ7wSwJk/w-d-xo.html :)