Introducing window functions in DAX 

Phenomenal intro and demonstration, sir!

Thank you Alberto. Great work as usual from SQLBI. The best syntax for window functions is best described by Jeffrey Wang. Function ( , , , , , )

Super ❤ I had the decision not to even try window functions until I see your video about them. I cannot wait to see more specially the performances related video. I will start using them today itself. Thank you Alberto for everything.

Muchas Gracias Alberto, es maravilloso todo lo que va mejorando power Bi y sus funciones , con tantas actualizaciones simplemente tocara solo dedicarse a ello , excelente explicacion como siempre!!!

Thank you very much, @SQLBI! Watching Alberto , materializing his ideas is always a great pleasure to me. I have a little question / consideration about the syntax used in OFFSET() demo -- [20:55] -- using of ALL() function to get Dates. I mean that ALL() returns additional blank row, so it will add a blank row in the head of the 'DatesAndSales' table, that will yeild one false positive to our results set.

Amazing, Alberto! 🤗😍 Wonderful as always. I have always learned a lot from you. I use Dax's book as my holy bible. Hugs from Brazil, Victor.

Thank you very much for the clear explanation

All right. Need to use these new functions.

great video as always, thanks. The INDEX function would have been incredibly useful when i once had to make a cash flow analysis based on a table from an accounting ERP system that would create 13 records with cash balances each year - one on the last day of each month what the cash balance is but one additional record on the first day of the year each year

Great video. Thank you :)

Very useful video. Thank you very much 😍

It is possible to use WINDOW function and sort by calculated column [@Sales]. The from_type/to_type parameters in that case must be specified as 'ABS'.

Thank you ❤

Excellent video as alwasy, very informative and insightful. I was wondering though, in the last example for WINDOW, why do you add the second ORDERBY column? Shouldn't YearMonthNumber be enough to sort the table, what additional need is there to add YearMonth to the sorting? Thanks!

Sqlbi, please do your customer retention piece using win functions! Keen to see if there are performance improvements. Love your work 👏

🔥🔥🔥

Please update us if the apply semantics white papers are completed for us to read. Thanks for this fantastic video.

Great video, thank you. Your content has had a profound impact on my DAX journey. I was attempting to pass a filtered table (topn) into the relation parameter of INDEX function but I kept getting an error. "Relation parameter may contain duplicate rows. This is not allowed." The table didn't have duplicates however. I know I am missing something.

1:59 INDEX 18:03 OFFSET 29:48 WINDOW

I always do axchange curancy. 1.8 milion sometimes 2 excel books rows in my excel . this technic is very usefull. Great video, I tried it in my own table, but i get blanck rows. Checked all formats and so on....

Thanks for the amazing fresh vedios for window functions!!! Can't wait for it to get generally available. Also wondering when Bravo's Time Intelligence Measures are going to update with the new window functions 🤭

In the rolling average graph during window function, I can see the rolling average value is extending to 5 months in future. How can I prevent that from hapenning?

Awesome. No half-baked, rushed demos just to get something out because it got released 10 seconds ago 👏🙂 Can I ask why you alternate between using ADDCOLUMNS+SUMMARIZE in some demos and SUMMARIZECOLUMNS in others? I read on a forum that SUMMARIZECOLUMNS should not be used in measures since it does not enable context transition but you seem to use it in your demos within DAX studio. Would it not cause less confusion for students if you stuck with ADDCOLUMNS + SUMMARIZE? Thank you.

For a moment I thought FUILKTER() was a version of FILTER() in German. 😀

Is my understanding correct that a limitation of window functions, particularly from a visualisation standpoint, is the necessity of including a specific column (e.g., ReportingDate) in the visual in order to obtain a value for a previous date? I aim to avoid including that column. Are there any potential workarounds for this? To illustrate, let's consider a straightforward report featuring a table visual with columns sourced from a fact table: ProjectID, ActualDate, and PreviousActualDate (a measure). The report includes a dropdown filter (ReportingDate) derived from a dimension reporting date table, establishing a one-to-many relationship with the fact table, filtered to a single date. It seems challenging to utilise a window function to retrieve the PreviousActualDate in the table visual without having to include the reporting date column. I have attempted to do so, but it consistently displays as blank unless the reporting date is included in the visual. Are there any strategies or alternatives to address this issue? I've reverted to using a window function in a calculated column in the fact table for now.

Hi, can you tell me what kind of camera and lens you are using for your recording? Looks really good. thanks

hello, can windowing function on another type from date ?like i want to winfow sales by products

Each one of your videos is a pure goldmine of information. I wanted to check if this can be implemented in the following scenarios: If you want to calculate the maximum value of a measure for the relative 12 months, can the WINDOW function be utilized? I tried it but I just get the MAX of each specific year-month combination. but I need the single MAX of those 12 months to appear in the full selection. So something like: P3-2021 = 15 P4-2021 = 8 P5-2021 = 10 P6-2021 = 12 ... P2 -2022 =7 If within those 12 relative months, the highest value is 15 then for all those periods to return that dynamic MAX value, so the end result should be: P3-2021 = 15 P4-2021 = 15 P5-2021 = 15 P6-2021 = 15 ... P2 -2022 = 15 Can this be achieved with WINDOW or would you recommend a different function? I tried with calculatetable and previous date calendar but for some reason I get only the last period value as the MAX :( in this example that would be: P3-2021 = 7 P4-2021 = 7 P5-2021 = 7 P6-2021 = 7 ... P2 -2022 = 7

In pure SQL window(analytics) function can be used on table and on results (agregate). How can be achived in DAX? Make windowed calculation on result of group of calculations.

Whilst it's not always a strict improvement on the existing methods, would you say, if one is the in the process of "Mastering DAX" to opt towards these rather than the incumbent functions? In a vacuum, for the usual day-to-day use cases.

Hello! Will you Update Mastering Dax Course with this all new features??

Hello Dear, Can you share the data you used for this example? pls

🎉

How can we use the Relation arguments in all these functions?

Do you have any courses on Udemy?

BTW This Thumbnail so cute

Sir, I'm Unable to calculate moving/running average using Average Cost moving = AVERAGEX( WINDOW( 1,ABS, 0,REL, SUMMARIZE(ALLSELECTED('Custom Calendar Table'), 'Custom Calendar Table'[FY], 'Custom Calendar Table'[Month Name]), ORDERBY('Custom Calendar Table'[FY], ASC, 'Custom Calendar Table'[Month Name], ASC)), [Actual Cost] ) Results is Average Cost moving Actual Cost Month Name FY 220599.2747 220599.2747 Dec FY 22-23 219440.6214 219440.6214 Jan FY 22-23 216470.4219 216470.4219 Feb FY 22-23

You are looking more handsome with your beards

Why does one need 40 min videos for something that should be so simple, yet powerful such as Window Functions? This is all you need to know about the issues with DAX. Way more powerful: produce a series of videos that describes WHY one would need to invest time in DAX, as apposed just writing code in SQL.

Do you have any courses on Udemy?