The new OFFSET function to return the previous row (production cycle times: a use case)

[datadonuts]

Hi community,

working with serial data you might know, how difficult it is, to calculate the previous row value. It can be achieved using the EARLIER function in a calculated column, but only if you have a column with a continuous sequence of numbers like an ID, date, or time. However, having millions of rows, it is not always best practice to work with calculated columns because it increases your data volume unnecessary. Furthermore, using CALCULATE and EARLIER in a direct query, you quickly find tricky obstacles to achieve your desired result.

Here the new OFFSET function comes to rescue. It is a new function released I think in September 2022, which can help to overcome some of the common challenges to return the previous row and build your calculation on it. 

A very common scenario or use case is the calculation of cycle times in serial production, especially if the data only provides one time stamp for a part e.g. when leaving the line. This is my approach to achieve this calculations and presenting it in a small dashboard. Here are the DAX measures I came up with:

1. Calculate the actual time stamp

I tried to put the actual time stamp as a variable VAR in my main measure, somehow it didn’t work out. So it became the very first step.

0_Measure actual timestamp testdata = MAX(TestData[TimeStamp])

2. The main measure to get the previous row and the introduction of OFFSET

The main difficulty wasn't the OFFSET function itself to get the previous row. But I had to learn later in the process, that by filtering the data e.g. on production date or production hour I got wrong results. That happened because filtering the data “removes the previous row” from the first record in the measures context thus it calculates the cycle time for that first part e.g. in a selected day wrongly. A calculated column using EARLIER avoids that, because the previous timestamp exists even by filtering the dataset. So I had to extend the measure as follows. Note that the OFFSET function need to have an ORDERBY statement.

1_Measure OFFSET prv row testdata =
VAR _prevrow =
CALCULATE(
    [0_Measure actual timestamp testdata],
    REMOVEFILTERS(TestData[Prodhour]),REMOVEFILTERS(TestData[CC_Proddate]),
    OFFSET(
        -1,
        ALLSELECTED(TestData[Part ID]),
        ORDERBY(TestData[Part ID], ASC)
        )
    )
RETURN
_prevrow

3. Calculate the cycle time in seconds

Now it’s pretty straight forward to calculate the cycle time in seconds using the actual timestamp and deduct the previous one calculated before

2_Measure OFFSET cycle time testdata (sec) =
IF(ISBLANK(TestData[1_Measure OFFSET prv row testdata]),0,
([0_Measure actual timestamp testdata] - [1_Measure OFFSET prv row testdata]) * 86400)

4. Get the average cycle time in the context of the production hour

Now came the tricky part; somehow the OFFSET function “resists” the filter context and returns wrong results by putting it into a (table) visual. So I needed to get the help of a virtual table in order to calculate the average cycle time in the current production hour.

3_Measure average cycletime testdata =
VAR _table1 =
ADDCOLUMNS(
    SELECTCOLUMNS(
    TestData,
    "partid", TestData[Part ID],
    "prodhour",HOUR(TestData[TimeStamp])+1),
"cycletime",[2_Measure OFFSET cycle time testdata (sec)])
RETURN
AVERAGEX(_table1,[cycletime])

5. Getting the average daily cycle time

For a card visual I want to show the average cycle time for the day, so I created another measure for that.

5_Measure daily average cycle time =
CALCULATE([3_Measure average cycletime testdata],
ALLEXCEPT(TestData,TestData[CC_Proddate]))

Now we are pretty much done, the further measures are just for supporting some more KPIs, e.g. the PPH (parts per hour) or the cycle time deviation from a imaginary target (you can check it out in the pbix file).

You might think all of that is pretty complicated instead of using EARLIER in a simple calculated column and you might be right. But I wanted to learn more about this new function and challenge myself with some more advanced DAX.

Please be so kind and comment, if you like it, or if you have suggestions, how solve this challenge in a different way.

Here the dahsboard 

https://app.powerbi.com/view?r=eyJrIjoiMzY2Njg5YTQtYzMxNS00ZThmLWE1MGUtY2NiMWQ5ZjU2OGY4IiwidCI6IjU0O...

or get the pbix here 

The new OFFSET function in Power BI to calculate production cycle tim… · andysuzhou/PowerBi@90d9cce ...

Thanks and have fun with DAX.

@Greg_Deckler what do you think?

[parry2k]

@Greg_Deckler Totally agree with you and thanks for the reference to my #video. We all get carried away when a new feature gets introduced whether it is a new DAX function or anything else.

Since the OFFSET function is not been officially announced and we don't what will change when it is announced but given what we have seen so far I have no single use case of this function, and I worked on almost 100+ projects, and I cannot think of any single project which I will change because this new functionality is available.

At end of the day, the aim of my videos is to look into how effective these new functionalities are, and not to get carried away but to find out where these functionalities can fall apart. These are good features/enhancements but always have some side effects that never get highlighted.

Having said that, in the next coming weeks (maybe days), I will also have another video that will show why/when the highly talked about feature will not work. 🤐 Stay tuned!

[Greg_Deckler · Accepted solution]

@datadonuts First, this is a fine post and very well done. However, I am not a fan of the OFFSET function and I have grave concerns if this is the path Microsoft is taking with visual level calculations. The problem, once again, is self-service visualization. @parry2k (Perytus) has a good video on this actually (YouTube). The basic problem is that DAX has no real sense of the visual layer. So if a user sorts the visual differently, OFFSET is going to make no sense in many circumstances. I cover the basics of this in the following video. Bottom line, trying to make DAX comprehend the visual layer is a recipe for failure and disaster. Visual level calculations should be their own thing and not DAX. It's the same mis-guided behavior and wrong thinking that has resulted in broken measure totals for table and matrix visuals. In my opinion of course.

[daXtreme]

@datadonuts 

Such calculations had been performed long before OFFSET existed. And it's not that hard, after all, to come by the correct formula that uses neither OFFSET (obviously), nor EARLIER which has been deprecated a long time ago as there are much better ways to achieve what the function achieves, namely VARIABLES.

Getting the previous row is nothing else than finding the maximum number (or date) that does not exceed the current one. And this can easily be achieved by taking the max of suitably filtered data.

Also, as much as I remember from a vid on YT by alberto.ferrari@sqlbi.com, there are some caveats regarding the use of OFFSET one should have in mind when trying to use this function. Such caveats are non-existent when using the method I described above.