The new OFFSET function to return the previous row (production cycle times: a use case)
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=eyJrIjoiMzY2Njg5YTQtYzMxNS00ZThmLWE1MGUtY2NiMWQ5ZjU2OGY4IiwidCI6IjU0OGVmYThjLWI1MzEtNDRjOS05MGY4LTAzZDc4MTdkMzdmZSJ9 or get the pbix here The new OFFSET function in Power BI to calculate production cycle tim… · andysuzhou/PowerBi@90d9cce · GitHub Thanks and have fun with DAX. Greg_Deckler what do you think?
