Consecutive days with no sales - Unplugged #18
HTML-код
- Опубликовано: 17 окт 2024
- Patrick LeBlanc from Guy in a Cube asked how to compute the number of consecutive days without sales. A good topic for a new unplugged video, with Patrick as a special guest!
Read more about the "unplugged" format: www.sqlbi.com/...
#unplugged
![BRASIL vs. PERÚ [4-0] | RESUMEN | ELIMINATORIAS SUDAMERICANAS | FECHA 10](http://i.ytimg.com/vi/tbyOJKXfQnI/mqdefault.jpg)
Amazing Alberto imitating Patrick 🤣🤣🤣...exepcional video!
Simply Excellent! This is precision DAX Engineering
Brilliant intro and some very helpful DAX. Also, you could never be boring Alberto!
These crossover videos are priceless. I really get a kick out of watching you guys riffing on each other's "rituals" and "sacred words" from Primal Branding. Those are such powerful engagement techniques on their own, and you guys take it up several notches from there when you collaborate like this. Bravo.
Wonderful video.... The approach to the problem is very very nice ..we can see how you think and also it is a great learning to solve optimization problems ..it is like a jackpot video which solves all the problems in just 30 minutes .I can't believe Alberto Ferrari can act as well . 😂🤣... fantastic collaboration.. with Patrick.
you are clearly DaBoss! - amazing troubleshooting and explanation , step by step.
I wish I would have had a teacher like Alberto in my studies 25 years ago. So amazing! I hope I can pass on some of that to our new employees.
L'eroe di cui abbiamo bisogno, ma che non meritiamo!!
Grazie sempre eccezionale!
Master piece.
So much knowledge and adjustments to dax code in just several of minutes.
10x!!!
Thank you very much, Two times you save my work with your amazing and complete videos.
Awesome, thank you! It is really helpful having you explain your thought process as you work through optimizing the code.
I have been literally working on this problem for the the last few weeks! Thank you!!!
Saved me again, while teaching me new things in the process.
Greatly appreciated.
this was really cool blending together the american coolness and the italian class together
Nicely done. Watching someone work thru problems makes it easier to follow. And I feel smarter since I saw the REMOVEFILTER problem earlier than Alberto!
Congratulations Alberto, It was the best DaX class, which I attended, challenging
And I do like your videos.. you never ever ever disappoint. I am so grateful for all you do to help us lesser mortals.
I´m a huge fan, this Dax is going to be very helpful in creating Sales reports. Thank you very much. I have been improving my Dax Skills gradually thanks to Alberto and Marco
Fascinating how you broke down the problem into small steps and then how you used DAX and DAX Studio to build the measure and optimise it. Really helpful in how to problem solve and think in DAX.
100% agree Sue!
Oh my goodness! You guys doing such a good intro using each other's scripts! Haha!
What an amazing collaboration🤣
thank you so much!
Brazilian greetings.
This intro was amazing! Great work.
Jajaja. This was a great intro. And a great video, as always.
Such a inspiring content for junior like me to see a pro at work in real time alongside code optimization. Keep up the good work :)
Thanks Alberto. You are the best :)
Wonderful!!! Just what I needed, and also, I’m working on the counting of sales dates against the same sales days count from last year. By the way, can you share where you bought your microphone!!!??? And the details of it….Please?? It looks not heavy at all!!!! Thank you Alberto and Patrick!!
You can find it here: www.sqlbi.com/blog/alberto/2021/02/14/on-my-recording-gear/
Another Excellent video, you make it looks so easy but it’s definitely not. Thank you
Love you guys. Really insightful
Love this crossover video style!!
As always best content❤, The crossOver was Lit.......suggestion: why not Hollywood Alberto?
This is really great stuff AND pretty much exactly opposite what I'm trying to accomplish. How do I change the DAX to count the days where there were sales? (My use case is counting how many consecutive days an employee worked ... to help us maintain compliance and ensure we aren't stressing our employees.)
It is getting better and better LOL.
Really helpful video. Thank you so much !
Any idea on how I could get the latest value of the measure for each serie of period without sales ?
This is brilliant!
From 4 seconds to half second add the prefix "Super" to "brilliant"! :)
Although 4 seconds mean "forever" to Alberto, it means "super fast" to most users (in my company). 😁
May I ask one question?
For calculating the first date ever, why don't we remove filter to date table only? If we remove filters to every table, does it return the first date ever for all products instead of the color selected?
Appreciate your advice.
You're right about removing the filters - clearly, it depends on your requirements.
@@SQLBI thank you so much for your prompt reply! 🙏🏻👍🏻
But what if your date table includes earlier dates not necessarily pertinent to sales? Such as employee hire date or birth dates? Removing the filter on the Date table would give you an artificially early date.
That was sooo good. Great explanation!!!
Not as good as the question! :)
Awesome guys!
Great video as always, I really owe my understanding of Dax to you guys. I just wonder here regarding firstdate. From my understanding removefilter on date is still too much as it returns always the first date in the date table (here it seems to be in 2010), however if I start my interval on for example 01/02/2020, I only want the first day before my selected interval. Therefore firstdate = min('date' [date]) - 1 should be better, or am I missing something?
If you need just a value use MIN, whereas FIRSTDATE is a table function it is used only when you filter a single value in a CALCULATE filter argument.
Nice video guys .... how would you go calculating number of consecutive weeks / days in top 5 ... something like a billboard...
Great video! How can you get the consecutive days WITH sales. I have a file with no 0 values and i cant figure it out how to make it work. Any tips?
Awesome!
Wow l love it. Can l request a slightly different problem solution?
What about computing when days have NaNs? Please do a video about creating a machine learning model on data that has nans in some of the days. For the nans perhaps use the avarage of other days in different months for that day. Please please 🙏
Wao... What's a intro same to same...
Like Patrick
Hello Alberto, great video, thank you very much !
I would like to ask how to count month instead of dates.
I figured out that i can create a column with month (and count the month numbers) but when it turns to the next year it is not counting the right way.
For example if target was not reached 3 months in a row (Nov 2021, Dec 2021, Jan 2022) i am getting only consecutive for year 2021 of "0" and "1" but the 3rd number is (-10) instead of "2". - the right consecutive should be: target not reached: Nov 2021 (0) , Dec 2021 (1) , Jan 2022 (2)
Any advice how to fix it ?
Thank you very much
Nice work on the optimization Albertrick! ;) Any specific reason why doing int(date2-date1) instead of datediff() ?
Faster and same result - DATEDIFF is better for MONTH/QUARTER/YEAR when they match your requirements (check on dax.guide/datediff/ )
Thank you
I love this cross over, can we have more ?
it is corssover and perfect team work. 2 interesting DAX masters!
i do wonder what keyboard are you using, may you share the model ;)
You can find the keyboard model here: www.sqlbi.com/blog/alberto/2021/02/14/on-my-recording-gear/
In the coalece we should use FirstDateEver-1. If not it will look as though there were sales on FirstDateEver even though there was not.
Good catch, I think you're right
Hello SQLBI,
How can I do an incremental refresh in power bi?
Amazing, Alberto! Is there a book or class where you teach how to systematically approach DAX performance issued?
You can find more in these resources:
Book: www.sqlbi.com/books/the-definitive-guide-to-dax-2nd-edition/ (chapters 17-20 are all dedicated to optimization)
Video course: www.sqlbi.com/p/optimizing-dax-video-course/
Excuse me Mr Alberto i know it's not relevant to this topic but I'm trying to calculate a number but I don't know how to do it with dax or power query
I have a base salary for 2015 and it's raising by a fixed interest rate of 9% every year and i wanna get it's computed value to be in current year 2021
I did it in excel formulas with FV function and i was moving forwards or backwards
Can this calculation be done in dax or m code
You also have FV and other financial functions in DAX: dax.guide/functions/financial/
That was great! 😂
Curious as to a workaround if I do not have an "Order Date" column in my sales table
Where can we find the dataset you are using?
Why don’t you clear cache before you run queries? The queries might use cache, what do I miss? Thanks
Performance was not the first goal in this unplugged, and the bigger cost is formula engine (not backed by cache).
Can we do it when all colors arecselected?
I want to count number of days with no sales and not number of days with no sales. Can you please give me an idea of how to go about it?
Hi, I got it now...your video gave me a clue as to what I should do. Thank you!
There is a for loop at the end of the video to enjoy DAX, otherwise great video on the thought process!
It was intentional! :)
@@SQLBI 🤣
Professor Alberto
how to find the consecutive zero in a column irrespective of date
תודה רבה
you forgot to say"new vidoes by Marco and me" :)
"Code is not the fastest at half a second"... lol ... I would have settled at the 4 seconds.
CurrentDate could also be SELECTEDVALUE instead of MAX?
Only if you are slicing by Date. If you do that by month, it would fail. Actually, my code would produce strange results too... the result for an aggregation of dates should be defined by the users.
Yo ❤️, sounds familiar for me 🤣
indeed DAX is the heart of power bi
hahah aamazing #ForMoreAlbertoLikePatrick
It's always TREATAS ({"Alberto"}, watch)
The Dax men ☺️
Sir please ask subscribers for their problem and scenarios which they are facing when developing reports...
So most of them will tell you about their problems and scenarios so you can make videos on solving that problem...
So more the content, more the comments and more the views as well subscribers....
Because you have much expertise in DAX...
Thanks
I mean to start doubt clearing video series...
And put it the pbix file along with that video... So it will also useful
TCHAO!
The boring Alberto and his PC, 👍
Ha ha ha
Watching this now. Interesting and I"m wondering if ChatGPT 4 can solve this. Will try later. Thanks for all the work you put into this channel. You've helped me grow in the BI field.
Nice imitate