Offset function combined with field parameter
Hello, I would like to create a table that will show a change between 2 dates but requirement from the user is to be able to dynamically change time perspective view - so they would like to compare year to year / month to month / week to week / quarter to quarter / day over day. Since there is also week over week needed I cannot use built in time intelligence function. Till now I used additional table with dynamic date selection, however I thought that maybe it will be possible to write a metric with offset function and in the table itselt I could use field parameter. I created it but received info: Column [] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression. My metric is written as follows: Total Change = VAR Offset = CALCULATE( [Total], OFFSET( -1, ALLSELECTED('Dynamic Date Selection'[Dynamic Date Selection]), ORDERBY('Dynamic Date Selection'[Dynamic Date Selection], ASC) ) ) VAR PoPChange = ([Total] - CALCULATE( [Total], OFFSET( -1, ALLSELECTED('Dynamic Date Selection'[Dynamic Date Selection]), ORDERBY('Dynamic Date Selection'[Dynamic Date Selection], ASC) ) )) / CALCULATE( [Total], OFFSET( -1, ALLSELECTED('Dynamic Date Selection'[Dynamic Date Selection]), ORDERBY('Dynamic Date Selection'[Dynamic Date Selection], ASC) ) ) VAR Result = IF(NOT ISBLANK(Offset), PoPChange) Return Result Dynamic date selection is a table created by using field parameter function: | Dynamic Date Selection = { ("calendar_date", NAMEOF('calendar'[calendar_date]), 0), ("week_end_date", NAMEOF('calendar'[week_end_date]), 1), ("month_end_date", NAMEOF('calendar'[month_end_date]), 2), ("year_no", NAMEOF('calendar'[year_no]), 3) } Is it even possible to make changes in Change metric to be able to use it in table with field parameter in rows?
Offset not showing value no rows
Here is my requirement: I need to show the Gross of child 2 on the row of child 1. See visual below.... Parent ID Child ID Child Date Type Gross Offset Gross Offset Gross 1 1 1/1/2023 norm $100 $250 1 2 1/5/2023 xfer $250 $100 I've created an offset calculation against a summarized table and it seems to work. I can see the value in the 'total', but the value will not show on the rows. I assume because they're being hidden. This is a STAR schema and Parent/Child/Date/Type are all different DIM tables. Gross is calculated in the FACT table. Any help is greatly appreciated. This is my current measure: CALCULATE( [gross], OFFSET( -1,SUMMARIZE( Fact, Fact[Parent ID], Fact[Child ID], Fact[Date]) , orderby([Parent ID] ,ASC,[Date],ASC) ,,PARTITIONBY([Parent ID]) )
Offset column with a date
Hello everybody, here is my problem : I'm trying to offset a column so that i can have the time between operations (in powerbi dax) the thing is that i can do it with numbers but i dont know why i can't do it with text. Can someone help me do it please ? Here is a exemple of a table that i have ( the data is not real) with an added offset colum that i want to code via DAX Thanks in advanceHow to combine OFFSET and PARTITIONBY within many groups have different records by using DAX
Hello everyone, I face to this problem when I want to lag 1 rank each row for each group, but when I try to use offet I don't know how to implement this. You can see the detail in the picture my solution. Please help me because I'm not familiar with DAXThe 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?
The most efficient way to perform lookup in table
Hello guys, how are you? I'm having a very specific issue, and even knowing how to solve that in theory, I'm having serious performance problems, due to the obligatory use from a very heavy database - an azure cube developed by my company that provides data worldwide. I've come with an example in a fact table, where I have trips from loading vehicles with an initial weight in the beggining of the trip. I need to create a measurement (can't make new columns in the direct query) that calculates the initial weight for the next trip from this same vehicle. It's a very simple task with ALL filters and on, however the real table is not retrieving the data due to the mentioned size of the datamodel. I've been trying using the OFFSET funcion, but I'm not being able to make the logic work to my problem. Any suggestions? Below, the latest try I've made: Next trip weight = VAR vFrota = MAX('Shifts and Events'[VehicleUsedId]) VAR vData = MAX('Shifts and Events'[EventStartDateTime]) VAR vTab = FILTER( ALL('Shifts and Events'), 'Shifts and Events'[EventStartDateTime] > vData && 'Shifts and Events'[VehicleUsedId] = vFrota ) VAR vMenorData = CALCULATE( MIN('Shifts and Events'[EventStartDateTime]), vTab ) RETURN CALCULATE( MAX('Shifts and Events'[MeasureBeforeLoading(kg)]), FILTER( vTab, [EventStartDateTime] = vMenorData && [VehicleUsedId] = vFrota ) ) Below an example of how I'm trying to make the information appear: Thanks!
