.png){kind=small}
.png){kind=small}
In this writing, I want to share when may be the case I want to use the WINDOW DAX function in Power BI.
In December 2022 Power BI updates announcement, three new DAX functions are released. Those are OFFSET, INDEX, and WINDOW.
Among these three, the WINDOW DAX function caught my interest because it reminded me of one problem I saw on the Power BI Community quite a long time ago.
It was asking about how to write a measure that could show a reverse accumulate total. And I thought it would be fun to solve this by using the WINDOW DAX function.
Before I step further, I want to highlight that there are many other ways to solve the same problem. I just want to let you know that now there is a new way to solve the same problem by using the new DAX function, like below. Additionally, the performances that are shown in DAX Studio are slightly different.
My calendar table in the sample looks like this.
The sample datamodel looks like the one below. I attach this pbix file at the end of this blog post.
The measure that I write using the WINDOW function looks like below.
By the way, the things that I like about this function are,
I can indicate a relative current row by writing REL, or I can select an absolute row by writing ABS.
I can partition the range by writing PARTITIONBY parameter.
Please be careful when the month-year column is sorted by another column in the calendar table. In this case, all the related columns have to be included inside the measure, like below.
QTY yearly reverse accumulate total: = CALCULATE ( [QTY:], WINDOW ( 0, REL, // 0 and REL indicate every current row -1, ABS, // -1 and ABS indicate the very last row in the partition that is described in the last parameter in the function SUMMARIZE ( ALL ( 'Calendar' ), 'Calendar'[Year], 'Calendar'[Month-Year], 'Calendar'[End of Month] ), ORDERBY ( 'Calendar'[End of Month] ), KEEP, PARTITIONBY ( 'Calendar'[Year] ) // it partitions the range ) )
In order to have the same answer without writing the WINDOW DAX function, authoring like the below is one of many ways.
QTY yearly reverse accumulate total V2: = VAR _currentrow = MAX ( 'Calendar'[End of Month] ) RETURN IF ( HASONEVALUE ( 'Calendar'[Month-Year] ), CALCULATE ( [QTY:], FILTER ( ALL ( 'Calendar'[End of Month], 'Calendar'[Month-Year] ), 'Calendar'[End of Month] >= _currentrow ) ), [QTY:] )
Below is the result of writing with WINDOW DAX function in DAX Studio.
And, the below is the result of writing without WINDOW DAX function in DAX Studio.
There might be many better ways to show the same answer without writing the WINDOW DAX function, but for now, I would like to hand it over to readers to find out more.
I hope this helps to have fun playing with the WINDOW DAX function in Power BI.