[DAX] Time Intelligence vs WINDOW vs OFFSET

[ibarrau]

Before starting the article, we will leave a link to Microsoft's documentation. This way, you can familiarize yourself with OFFSET and WINDOW before starting.

https://learn.microsoft.com/es-es/dax/new-dax-functions

The applied analysis will be carried out on an Adventure Works in import mode. The first thing we are going to do is learn how to build some classic Time Intelligence scenarios with the new functions. Let's see examples for LY (Last Year), LM (Last Month), and YTD (Year-to-Date).

Last Year (LY)

Normally, to build this function we can use DATEADD or SAMEPERIODLASTYEAR. Let's see an example for a measure of [Net Price AC].

Net Price LY = 
CALCULATE(
    [Net Price AC]
    , SAMEPERIODLASTYEAR(TablaFecha[Fecha])
)

Now, let's see the translation to OFFSET

Net Price LY OFF =
CALCULATE(
    [Net Price AC],
    OFFSET(
        -1,
        ALL(TablaFecha[Año])
    )
) 

And WINDOW

Net Price LY WIN =
CALCULATE(
    [Net Price AC],
    WINDOW(
        -1,REL,-1,REL,
        ALL(TablaFecha[Año])
    )
)

By default, when we do not specify values ​​for ORDERBY and PARTITIONBY, it would take what is expressed in the relationship table. In this case, the distinct values of Year.

In a quick check, we can see everything is in line:

Last Month (LM)

In these cases, we can use both DATEADD and PREVIOUSMONTH. Let's continue with our example using Net Price AC.

Net Price LM =
CALCULATE(
    [Net Price AC]
    , PREVIOUSMONTH(TablaFecha[Fecha])
) 

Translating to OFFSET

Net Price LM OFF =
CALCULATE(
    [Net Price AC]
    , OFFSET(
        -1
        , ALL(TablaFecha[Año], TablaFecha[IdMes], TablaFecha[Mes], TablaFecha[Periodo])
        , ORDERBY(TablaFecha[Periodo])
    )
) 

WINDOW

Net Price LM WIN =
CALCULATE(
    [Net Price AC],
    WINDOW(
        -1,REL,-1,REL,
        ALL(TablaFecha[Año], TablaFecha[IdMes], TablaFecha[Mes], TablaFecha[Periodo]),
        ORDERBY(TablaFecha[Periodo])
    )
) 

The translation this time is a bit more complex. We require more than just the year to make the engine understand what represents a month. We can use the pair Month and MonthId or we can use Period. I expressed both to have the freedom for the measure to work for both columns in case of visualization. MonthId is the month number that gives order to the month name. For this reason, it is necessary to include it in the relative table we are going to explore.

Validating values glance:

Year-to-Date (YTD)

For this scenario, we are going to use the measure Quantity AC.

Quantity YTD =
CALCULATE(
    [Quantity AC]
    , DATESYTD(TablaFecha[Fecha])
)

OFFSET. At the moment, we do not have a measure with this function because the function performs a specific offset to retrieve the value of a row and not to add or accumulate a set of rows. There may be ways to execute the action, but they would likely require additional steps that make the resolution more complex. Since we are looking for the best way to solve this, I doubt this option applies.

Now, let's see for WINDOW

Quantity YTD WIN =
CALCULATE(
    [Quantity AC],
    WINDOW(
        1,ABS,0,REL,
        ALL(TablaFecha[Año], TablaFecha[IdMes], TablaFecha[Mes], TablaFecha[Periodo]),
        ORDERBY(TablaFecha[Periodo]),
        PARTITIONBY(TablaFecha[Año])
    )
)

The key this time is that the accumulation starts at the first absolute index and ends at the current relative index partitioned by year. This way, it accumulates values from the beginning of the partition to the last.

We validate the solution:

Diagnostics

Now that everything is arranged in the best way, let's measure the performance of our measures as SQLBI suggests. We start with a blank page in our PowerBI Desktop to have all cache memories cleared, and then we would turn on the Performance Analyzer to see the numbers of the visualizations clearly.

LY

Within the timeframe of each visualization for LY, we can see that there is not much difference in terms of performance. Although OFFSET took less time for the totalization, the details indicate that the DAX Query takes exactly the same time for each measure.

LM

For the Month, we already have a difference that arises mainly from covering columns for Month, MonthId, and Period. When I tested it with just Period, the three numbers were closer and less than 30 in DAX Query. Time Intelligence (TI) still won by a small margin.

YTD

Finally, when accumulating, we haven't found significant differences either.

Conclusion
The new functions have great power to navigate and bring values from a context. They can search by index or quantity in a certain order. They are very powerful for scenarios of searching under numerical or text indexes. However, the new functions are not better or work better for traditional date scenarios with an imported dataset. The new functions would NOT replace traditional Time Intelligence functions that we have been using for quite some time.

Be careful in Direct Query scenarios as differences have been noticed there, and Chris Webb demonstrated it in an article on his blog.

As always, you can find the test PowerBI Desktop file on my github. I hope the analysis has been useful to you.

Original post by LaDataWeb