WINDOW Function in ACTION I Examples in Power BI

In this video I put the new window function in action. I show case step by step how the function works with two very useful examples: 1) to create reference lines for subsections in a visual and 2) to create a moving average where the number of periods can be determined by the end user.
Enjoy this video and subscribe to always stay updated on my favorite Power BI tricks :)
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 ⏱️
---------------------------------
00:00 Intro
01:36 Example 1: Moving Average with the Window Function
09:34 Integrating a parameter to adjust the window size
12:18 Example 2: Using the window function to create reference lines for subsections
17:11 Integrating conditional formatting to highlight marks above and below references lines
18:47 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

[@JustRandomExplorer]

Great! to be honest every single of your videos are someway or another valuable. One of the best Power BI content creators on youtube. Thanks again!

[@HowtoPowerBI]

So nice to hear! Thanks a lot 😊

[@hainsay]

Massive agree. I watch SQLBI for the minutiae of detail of DAX and how it works, the most technical stuff from first principles, and I watch Bas for exposure to report components I wouldn't otherwise know about, and how to bring the whole thing together. I don't think I'd know anywhere near as much without both.

[@habuiah]

@@hainsay Oh yeah! The combo SQLBI + HTPBI is the gold content on KZbin!

[@IonbeamGreg]

I really appreciate how you explain DAX. Detailed, explaining what is is important and where could be our mistakes. A step by step visual explanation. thanks !

[@21121990jay]

Precise and unambiguous explanation. I'm sure even chatGPT wouldn't be able to explain this easily.

[@baskis69]

Thank you very much for sharing. You explain in a masterful way¡¡¡¡

[@carlwagner3838]

Thanks for the tutorial. I am very happy to see the Window function for creating moving averages over tables that have no dates. Wrote my first last night after upgrading to the Dec PowerBi desktop.

[@AgustinFernandez1009]

Your videos have helped me a lot, I notice how I started using PowerBI in a whole different level since I suscribed

[@ayoubaitelkadi7796]

You deserve +10 M subscribers and not +100K. Your enlightenment is greatly appreciated!!

[@kebincui]

The best DAX and Power BI content presentor 👍👍👍👍👍👍🌹🌹🌹🌹🌹🌹

[@stevenhampson8657]

Brillian Bas back again (from a real human). Really, really value your content - friendly approach, quality, detail, right level and the backing of the website with extra information and reference files.

[@HowtoPowerBI]

Thanks a lot for those kind words! 😊 made my day

[@GladiusArch08]

I watched it at the time and remembered it. Sure enough finally had to use it in one my reports. Thanks a ton! You're a life saver

[@kostaszogo21]

Give that man a daily show in a national broadcasting channel!!! once again Bas delivers 👏

[@HowtoPowerBI]

maybe one day... :)

[@mwaltercpa]

I love how your videos address technical DAX with a practical business case approach.

[@akshay1darve285]

Superb feature and Thanks bass u explained amazingly ...

[@ekaterinavolkova4348]

The best video I've ever seen about the window function in Power BI. And I really like your way of telling it-smooth, positive, without unnecessary excitement.

[@KyleCroucher]

You are the best DAX explainer BY FAR!

[@scobob]

This is fantastic. I already have a bunch of use cases in mind for this. Thanks for the clear explanation!

[@estebansuarezcastro7053]

Bas, this video is going to save my job this week. Excellent work!

[@joaorataoo]

Excellent presentation! I hope that that peregrine idea that IA would replace you is now forgotten. ChatGPT is very interesting as a support, to break a block, not to learn how to know how to do it. This is just my opinion, of course! Keep going and thanks!!!!!!!!!!!

[@Abiah]

18:57 Wow! This is great. Thanks for sharing 🎉

[@sayankarmakar609]

Sir your voices are great I learn everything from your channel. Sir can you make tableau video?.

[@pabloalonso4017]

This is by far the best PBI channel. Congrats and thank you for all the unique knowledge you have shared.

[@MVoss-nr6sd]

Yes ! Bas is back I agree @how to power BI channel is the best Thank you Bas

[@sherifffruitfly]

This is a very good video. Showing the need for the params by seeing what goes wrong is super helpful. Also taking it end to end with visualization is nice. Video would be enhanced by adding discussion of what the code IS doing in the "wrong" versions. I.e. the code isn't doing anything wrong - it's doing exactly what you told it to do. Understanding what you're telling PBI to do is the fullest learning. Performance vs old ways to do the same could also be discussed.

[@___________mrivan___________]

All the other approaches using DATESINPERIOD and DATESBETWEEN have failed for me. This is the only one that worked perfectly. Thank you!

[@J0n0nI]

Appreciate your videos content. I am really learning a lot from them. as your explanation to the functions and the visualizations made them so easy to follow. Thank you.

[@victorsalaspsalas3119]

You are great, thank you very much for your teachings, I am from Chile and a faithful follower.

[@HowtoPowerBI]

Wonderful! Thank you for watching Victor!

[@user-xi7cj3so8f]

it's great that you're back, otherwise I started to think that the previous video is really the last one😁

[@raeesg]

😂😭

[@HowtoPowerBI]

No worries, planning to stick around for a little longer :)

[@user-xi7cj3so8f]

@@HowtoPowerBI very good, your channel is the best

[@PowerBi_ExcelDashboards]

Great to have you back my brother

[@marcelolopes5684]

Very well explained, congrats!

[@premcst]

Spot on !! And Congratulations on 100K subs .. More to go ..

[@HowtoPowerBI]

Thank you very much!

[@mathiassaint]

Thanks for yet another great video! I especially love that u display the end result in the start of the videos! :)

[@atanasprodatanasov9233]

Brilliant video. Thank you so much!

[@josefedericopresno6668]

Great to see You again, Bas! Awesome video as always. Thanks for share your PBi knowledge

[@flyeagle320]

BAS it was very informative . Need time to understand . Thanks for the video :)

[@awarrentfa]

This was an excellently crafted lesson, with each aspect of the formula demonstrated precisely when it would be necessary and useful. Well done!

[@HowtoPowerBI]

Glad you enjoyed it!

[@earlnoli]

Lovely explanation thank you mate 👏🏻👏🏻

[@christianplesner9152]

Congrats on the 100.000 subscribers!! :-)

[@lubomirjagersky7327]

Wow! Thanks a lot for your effort. Your videos are awesome, I like your energy that you put into an explanation. I don´t need to speed up your video as many others... You create amazing content, I´m always looking forward for your next video...Really great work

[@udusegbe]

Awesome Function! And even more important, thanks once again for a very clear explanation video :-)

[@data-futureoil1435]

Awesome explanation... Would appreciate if you make a video on exponential weighted moving average ..

[@jamesshi9993]

Super good, well explained and easy to understand. Thanks!

[@Amr-Ibrahim-AI]

Thank you so much Bas for your detailed video 👌🤝👏👏👏

[@storybi4636]

I'm very interested to learn how the new functions and performance stack up against existing ways to write DAX. i.e. custom time with CALCULATE and Filter/All/Time. As someone looking at learning and adoption, it seems like the new functions are just as involved as the old CALCULATE with modifiers. Thanks again, I really enjoy your videos.

[@sebastienschoonjans9727]

Thanks for the great content !!

[@mabenba]

Amazing video!! Thanks for sharing your wisdom with us. I can't the same result as the second option replacing the quarters by the months. I need to upskill my DAX.

[@nseworld5521]

Like the way how you explain new functions!

[@nehikmetse]

Bas you are a gem, really good explanations and kick ass hidden tips and tricks. Really valuable presentation.

[@relogbr]

Great work Bas, thank you!

[@RubenCarlosCalderonValladares]

Really nice! Window Function is Flexible and easier to code!

[@richardmartin7904]

Awesome. I would love to see you get messy with more windows functions. Does this mean there is even wilder dax and dynamic calculations we can do? Will there be a theoretical argument for using DAX window over sql window? Which one is more efficient?

[@vebe7109]

Great examples!

[@sukoi2113]

Thank you for one more beautiful presentation.

[@HowtoPowerBI]

Very nice to see a comment from you in the new year!! Hope you had a good start

[@barttrudeau9237]

You are a great teacher! This has been really helpful

[@HowtoPowerBI]

Glad it was helpful! 😊thank you

[@user-ze4xc6tu4f]

this try & error Tempo so good!

[@LeighRadmore]

Congratulations on the 100k subscribers - thoroughly well deserved

[@HowtoPowerBI]

Thank you very much!

[@Saif_Ali_Khan_1301]

Brother how do you edit your videos, can you make a video on this topic, you editing is as awesome as your content

[@antique-bs8bb]

Really nice vid and I really learnt a lot. Love the deliberate teaching mistakes Just one bitty query. For a 3 month Moving average we used From -2, To 0. (time 3.30) For an n-month Moving average we used From -n, To 0. (time 11.20) where n is your [NR Months Value] parameter. So I reckon we are producing an n+1 moving average. Easily fixed mind.

[@EricaDyson]

Lovely! Thanks so much.

[@unnikrishnansanthosh]

double kudoz Baz, superb one

[@3danim8r1]

Thank you for sharing!..

[@HowtoPowerBI]

😊😎

[@soumyadeeppattanaik526]

Nicely Explained...Thanks☺

[@hussamamal]

Awesome video, Bas! Inspiring to see your vids, really. Question: How to use the MA measure to show expected total sales for next month, and then include that forecast in the next next month? You know, as in excel where it's a really simple cell-reference, I've found it quite tricky in DAX.

[@valentinaverissimo5584]

No Brazil temos alguns canais mto bacanas tbm sobre o tema

[@autobase16]

Brilliant!!!

[@lguzjim]

Excellent!

[@claudiozadorosny4722]

Around 9:11 you talk about if we want the first 2 months to be blank. I saw it over and over, but could you please show the DAX formula for it? Ty =)

[@ezekiel0606]

amazing content!

[@manjunatharalikatti2202]

Nice explanation .. Please explain, scenario 1. If we are on Jan 23, I want to start calculate moving avg from DEC-22, i.e always start moving avg from last month (Jan data should not be shown in report ) Present date Today()-1 from measure or in window function not working..

[@allmazd]

Thank you Bas for the explanation, it is pretty good as always! I have a question, what was the purpose to add the column month in the relation clause if it is also monthNO column in there and the column month has not been used in anywhere else?

[@danielslagter]

after you added the Avg line Q3 and Q4 has added to the visual even though they are empty, is there a way to hide that? to see only quarters with values

[@lubomirjagersky7327]

Thank you

[@HowtoPowerBI]

thanks a lot for the support!

[@juliom6260]

Thanks Bas

[@user-gm7tq9pc9f]

Awesome!!!

[@jonahjohnbaba]

Please the line chart can you do a video that you use a target to say if you achieve the target the bar is green and if not is red and then the target should recalibrate itself based on the gap yet-to be achieved. Thank you

[@jiawang1257]

Thanks!

[@HowtoPowerBI]

Thank you for supporting the channel!

[@464jam]

Hi, Nice video once again. Can you make a video to make "Skyline Chart" in Power bi , currently there is no custom visual to make such chart but i believe you can crack and we all learn from you. Thanks.😊

[@vram11]

Brilliant.... in short

[@martingoedhart2294]

Have yet to experiment with the Windows functions, but I'm afraid that while the intent is to make things easier, it can work confusing to introduce an alternative approach that works different from regular DAX theory. I wonder if they are slowly converting the language in a way that Windows functions become the standard approach or if there will always be multiple methods. The code for moving average is not easier at all compared to a simple; VAR LastDate = MAX(Dates[Date] RETURN CALCULATE([Measure], DATESINPERIOD(Dates[Date], LastDate, -3, MONTH) I'm sure though that there are some benefits that would require few lines of Window function compared to pages of DAX though. Curious to discover them.

[@Tyraelxadrias]

We awesome video!

[@valentinaverissimo5584]

Olá seus vídeos estão sendo mto bacana para o meu desenvolvimento...compartilha a sua paleta de cores...pls

[@datactive859]

I used to wrtie a measure of 7-day running average, but now I can use window function. maybe next time I can ask chatgpt to write for me.

[@spilledgraphics]

Professor of year: Bas.

[@HowtoPowerBI]

🤓😄 thanks!

[@switchfire]

Awesome video! Do you think the Window function is the best way to calculate moving averages? Is it possible to use these averages to 'predict' future values of dates? If I don't have values for future dates in my dataset, could I create them with a measure and incorporate those averages? Sorry for so many questions! 😝

[@claudiozadorosny4722]

Hey, if possible, please make a video on how to calculate the moving average of last X days where it only calculates days that had transactions. Example: there were transactions in the month of january on days 10, 13, 17, 23. The moving average should only start appearing on the 23rd and should be sales (or whatever measure) of those 4 days divided by 4. Thx in advance

[@mcdonaldikisielmeow]

DAX GURU!!!

[@lambertusdekker]

Wederom klasse, Bas

[@fraggle200]

@ 12:18 when you point over your shoulder, there's no visualisation on screen.

[@natsarev]

Didn't quite understood why is it better then calculate + filter? Is it faster or..?

[@HowtoPowerBI]

seems to be faster (still have to do some checks for myself) .. and I believe it is an attempt to make dax easier (I think more functions will follow)

[@justlearnitquick]

How can we calculate rolling average if windows function was not available in pbi dax ?

[@HowtoPowerBI]

here you find an example www.sqlbi.com/articles/rolling-12-months-average-in-dax/

[@MVoss-nr6sd]

Bas I have a question how do you deal with the dax function Count and Distinct count if you have to count people

[@M4rz10]

Grazie

[@therainman8703]

Thanks for your help and appreciate you for helping others. I have my devices data in adls gen2 in folders & sub folders . I have made a dashboard for one device by using the data source as adlsendpoint/one device .. how can I provide an option in power bi report, like user can select device id from drop down which automatically changes the data source path adlsendpoint/newdeviceid and refreshes charts One device - one dashboard charts Need option to select new device from report (new device is a new folder in adls ,) is this possible ...please help

[@davidtanchon3067]

Hi, I would like to use this function, but it's not reconized by Power bi desktop (version Dec 2022)

[@azharuddinluhar9667]

Please show some applications of INDEX() function.

[@HowtoPowerBI]

Added an example in the download file where i use it to return only the first row in a partition

[@sathishkumar-sp3hq]

Hey Bas , long time no videos ... Would love to see something on HTML custom visual to bring in some design ideas.

[@jidi10]

Getting a "The expression refers to multiple columns" error for the Summarize function. Tried switching away from Dax date table but still has error, any ideas?

[@magnolita999]

Would you know how to design a report where you see "best man for the job" based on how much experience they had working on projects in the past? You would select various categories or subcategories of projects that would "spit out" the best man for the job?

[@sebastienschoonjans9727]

Is there any of you folks which could tell me how i could do this by Item Reference per Date? i do not see how to add my Items with the Calendar Date and ALLselected function

[@barte894]

Hi. I have some question. I need to make slicer panel with percentage intervals. Example: 30% Is it posaible to do that in power Bi?

[@autobase16]

Yes why not

[@barte894]

@@autobase16 but how? ;)

[@vijayvizzu1]

Is there any alternate functions available for Window function ?