Power BI — Window Function
The Windows function is one of three new DAX functions that were released in December 2022. Using WINDOW, a slice of results is retrieved using either absolute or relative positioning.
The WINDOW function can be defined using a combination of functions such as FILTER, ALL, and VALUES based on the requirement.
The Window function reads all of the data from the table provided by the <table> parameter and divides the records into distinct divisions using partition-by-columns’ unique values. Using the order-by-columns and sorting instructions, arrange the rows within each partition. Returns all the rows between a lower bound and an upper bound, depending on the current partition and the current row.
Instead of being pushed to the data source, DAX functions like Window’s are executed within the DAX engine. These DAX functions have shown significantly improved performance compared to existing DAX expressions, particularly when sorting non-continuous columns is necessary.
The WINDOW function in DAX has opened up a whole new world of possibilities for Power BI users, giving them the power to perform complex calculations and analyses on sets of data in a way that is efficient, uniform, and elegant. In addition to running totals, rolling totals, month-to-date, quarter-to-date, year-to-date, and averages, window functions can be used for many other functions as well.
Format
WINDOW ( <from>,<from type>, <to>, <to type>, <relation>, <order By>, <blanks>, <partition By> )
Model: I am using the standard sales model, which I am using for all my videos and blogs. Sales fact with a key measure [net], joined with dimensions: Item, Geography, Date, and Customer.
Date Table Script
Date = ADDCOLUMNS(CALENDAR(date(2018,01,01), date(2020,10,31))
,"Month Year", FORMAT([Date], "MMM-YYYY")
,"Month Year sort", FORMAT([Date], "YYYYMM")
, "Year", YEAR([Date])
,"Qtr Year" ,FORMAT([Date],"YYYY\QQ"),
"WeekDay", FORMAT([Date], "ddd")
,"Month", FORMAT([Date], "MMM")
,"Month sort", FORMAT([Date], "MM")
)
Let us create a visual using the Date and net.
We will also create our first Windows measure for Rolling 2 days and add to it.
Rolling 2 = CALCULATE([Net], WINDOW(-1,REL,0,REL,
ALLSELECTED('Date'[Date]),
ORDERBY('Date'[Date]))
)
Observe that it is the sum of the current and previous days.
In the same way,
you can create a rolling 30 measure
Rolling 30 = CALCULATE([Net], WINDOW(-1,REL,0,REL,
ALLSELECTED('Date'[Date]),
ORDERBY('Date'[Date]))
)
Month-year text cannot be sorted, so we need to use a sort column in the formula to create a rolling months measure
Rolling 2 Month = CALCULATE([Net], WINDOW(-1,REL,0,REL,
ALLSELECTED('Date'[Month Year sort], 'Date'[Month Year]),
ORDERBY('Date'[Month Year sort]))
)
Rolling 12 Month
Rolling 12 Month = CALCULATE([Net], WINDOW(-11,REL,0,REL,
ALLSELECTED('Date'[Month Year sort], 'Date'[Month Year]),
ORDERBY('Date'[Month Year sort]))
)
For running total/cumulative we have to start from the 0/1 abs(absolute/fixed) position. In this, I am building the formula using date. By using date I Can solve cumulative on all the above levels like week, month, quarter, and year.
Cumulative Total = CALCULATE([Net], WINDOW(1,ABS,0,REL,
ALLSELECTED('Date'),
ORDERBY('Date'[Date]))
)
Date Wise running total
Now, if we control this cumulative formula using partition by, we can get MTD, QTD, and YTD
MTD = CALCULATE([Net], WINDOW(1,ABS,0,REL,
ALLSELECTED('Date'),
ORDERBY('Date'[Date]),,PARTITIONBY('Date'[Month Year])
))
Same way, you can have QTD, YTD
My Medium blogs can be found here if you are interested
Click here to access all my blogs and videos in a jiffy via an exclusive glossary using Power BI. Please like, share, and comment on these blogs. I would appreciate your suggestions for improvement, challenges, and suggestions for topics so that I can explore these in more depth.
In addition, I have over 500 videos on my YouTube channel that are related to Power BI, Tableau, and Incorta. With the help of these videos, you will learn hundreds of DAX and Power Query functions, in addition to hundreds of Power BI, use cases that will assist you in becoming an expert in Power BI. Make sure you subscribe, like, and share it with your friends.
Master Power BI
Expertise in Power BI
Power BI For Tableau User
DAX for SQL Users
Learn SQL
