Introducing window functions in DAX

Great video. Thank you :)

Very useful video. Thank you very much
😍

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.

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.

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.

Thank you Alberto. Great work as usual from SQLBI.
The best syntax for window functions is best described by Jeffrey Wang.
Function ( <row-selection>, <table>, <order-by>, <blanks>, <partition-by>, <match-by> )

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

🎉

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

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

Thank you ❤

You are looking more handsome with your beards

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

🔥🔥🔥

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!

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

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 🤭

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

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 very much for the clear explanation

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

BTW This Thumbnail so cute

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.

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.

All right. Need to use these new functions.

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

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.

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

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