In this writing I like to share how I learned WINDOW DAX function in Power BI. Over the past few months, I’ve had the opportunity to work on several projects that required dynamic calculations, such as analyzing the recent three months’ cumulative data, identifying the top 5 sales data, and calculating Year-to-Date (YTD) values. While exploring solutions, I discovered how the WINDOW function could simplify these tasks by defining ranges and sequences in a way that feels intuitive and highly adaptable.
Despite having powerful DAX functions like DATESINPERIOD, TOPN and DATEYTD available to me, I found that the WINDOW function introduced a level of clarity and flexibility to my DAX writing.
It might not always bring a significant performance boost compared to traditional methods, but its structured approach makes the logic easier to understand and maintain.
In this blog post, I’ll break down the key parameters of the WINDOW function—from, from_type, to, to_type, relation, and orderBy—and illustrate how they work together to empower my Power BI analyses. While these are the primary parameters to focus on, the WINDOW function offers additional options that add even more versatility. Those, however, are a topic for another time and will be explored in future blog post.
Understanding the Syntax
At its core, the WINDOW function operates to result a specified range of rows or time periods within a table. The syntax reads as follows:
WINDOW(
from, from_type,
to, to_type,
relation,
orderBy,
partitionBy
)Each parameter serves a distinct purpose, and their combined configuration allows for precise control over the rows or periods to be analyzed.
Breaking Down the Parameters
1. from and from_type
The "from" parameter defines the starting point of the range, while "from_type" determines how this starting point is interpreted.
from: This can be any integer value or a function that dynamically calculates a position. For instance, “-2” indicates starting two rows or periods before the current one.
from_type: Options include REL (RELATIVE) or ABS (ABSOLUTE).
Use REL for offsets based on the current context. For example, starting 2 months back in a rolling analysis.
Use ABS for fixed positions within the dataset. For instance, position 1 would always point to the first row or the earliest date.
Example:
To capture the range starting two months ago:
from = -2,
from_type = RELTo capture the range starting from always the starting month:
from = 1,
from_type = ABS2. to and to_type
The "to" parameter defines the endpoint of the range, and "to_type" works similarly to from_type in specifying its context.
to: Similar to from, this can be an integer or a dynamic function. “0” indicates the current row or period.
to_type: Options are the same as for from_type. Use REL for offsets or ABS for fixed positions.
Example: To end the range at the current month:
to = 0,
to_type = RELCombining the above, you can define a range spanning two months back to the current month with:
WINDOW(-2, REL, 0, REL, ...)3. relation
The relation parameter allows you to specify how rows or periods are grouped. This is crucial for scenarios where partitions like years or categories are involved.
relation: Use this parameter to define the partitioning logic. For example, ALL(Calendar[Year]) ensures the WINDOW function operates within a single year.
ALL(Product[Product Name]) ensures the WINDOW function operates within all products
Example: For calculating TOP5 product name data, the relation could group rows by product name, ensuring the measure resets with each product name:
relation = ALL(Product[Product Name])4. orderBy
The orderBy parameter specifies the sequence in which rows or periods are sorted, making it essential for cumulative or TOPN calculations.
orderBy: This is an expression defining the sorting logic.
For instance, ORDERBY( Calendar[Date] ) sorts rows by Calendar[Date] column in ascending order.
Example: To identify the top 5 sales amount:
orderBy = ORDERBY( CALCULATE(SUM(Sales[sales_amount])), DESC )Or,
orderBy = ORDERBY( [Sales Total measure:], DESC )In some scenarios, I needed to sort by two conditions, and I could write like below.
orderBy = ORDERBY( Calendar[Date], ASC, [Sales Total measure:], DESC )When combined with the appropriate from and to parameters, this ensures that the WINDOW function operates on the TOPN rows by sales amount.
Practical Applications
Here are some examples of how the WINDOW function can be used to simplify dynamic calculations:
Recent three Months Cumulative Data
CALCULATE (
SUM ( Sales[sales_amount] ),
WINDOW (
-2,
REL,
0,
REL,
ALL ( Calendar[Year-Month name], Calendar[Year-Month name sort] ),
ORDERBY ( Calendar[Year-Month name sort], ASC )
)
)Top 5 Sales Data
CALCULATE (
SUM ( Sales[sales_amount] ),
WINDOW (
1,
ABS,
5,
ABS,
ALL ( Product[Product Name] ),
ORDERBY ( [Sales Total measure:], DESC )
)
)Year-to-Date (YTD) Analysis
CALCULATE (
SUM ( Sales[Amount] ),
WINDOW (
1,
ABS,
0,
REL,
ALL ( Calendar[Year], Calendar[Date] ),
ORDERBY ( Calendar[Date] ),
PARTITIONBY ( Calendar[Year] )
)
)Please note that the behavior of the measures described above can vary depending on the filter context applied to the report page. The configuration of filters, slicers, or other contextual elements within the report can influence how the WINDOW function processes and returns results.
Summary:
The WINDOW DAX function offers a flexible and intuitive way to define ranges for calculations. While traditional functions like DATESINPERIOD, TOPN and DATEYTD remain valid choices, WINDOW function’s parameterized approach makes it easier to articulate and adapt logic. This is especially useful when working on complex datasets or when clarity in DAX authoring is paramount. Experimenting with the WINDOW DAX function has been a rewarding journey. Whether it’s defining dynamic periods or sorting sequences with precision, this function enriches my Power BI toolkit.
I hope this helps enjoying writing the WINDOW DAX function in Power BI as much as I have!
Comments