Help with the Offset function

[GA2]

Trying to figure out the correct way to create the following column (in red) using the offset function with respect to equip_id partitions. The column "stat code" is a calculated column, dont know if that matters at all but any help you could provide is greatly appreciated. Thanks

EQUIP_STATUS_DATE	EQUIP_ID	CURRENT_FAC_ID	Stat Code	OFFSET	EQUIP_STATUS_REASON_DESC
2/26/2023 14:39	281683	14	Status 1	Status 2.1
2/25/2023 16:01	281683	14	Status 2.1	Status 1	Miscellaneous
2/16/2023 19:28	281683	14	Status 1	Status 2.2
2/15/2023 16:56	281683	14	Status 2.2	Status 3	Scheduled Maint
2/14/2023 5:47	281683	14	Status 3	Status 1	Mileage
2/11/2023 6:21	281683	14	Status 1	Status 2.1
2/10/2023 15:12	281683	14	Status 2.1		Miscellaneous
3/5/2023 14:00	281682	14	Status 3	Status 2.1	Mileage
3/3/2023 17:54	281682	14	Status 2.1	Status 3	Miscellaneous
3/3/2023 13:46	281682	14	Status 3		Mileage
2/18/2023 9:32	281681	24	Status 5	Status 5	Warranty
2/18/2023 9:32	281681	24	Status 5	Status 5	Warranty
2/18/2023 9:32	281681	24	Status 5	Status 2.1	Warranty
2/18/2023 9:32	281681	24	Status 2.1		Running Repair
3/2/2023 21:16	277561	14	Status 2.1	Status 2.1	Running Repair
3/2/2023 21:16	277561	14	Status 2.1	Status 1	Running Repair
2/28/2023 18:57	277561	14	Status 1

[Sahir_Maharaj]

Hello @GA2,

Could you please try: 

Offset Column = 
VAR CurrentEquipID = [EQUIP_ID]
VAR CurrentRowIndex = ROW()
VAR PrevRowIndex = CALCULATE(
    MAX('Table'[Row Number]),
    FILTER('Table',
        'Table'[EQUIP_ID] = CurrentEquipID &&
        'Table'[Row Number] < CurrentRowIndex &&
        'Table'[Stat Code] <> BLANK()
    )
)
RETURN
IF(ISBLANK(PrevRowIndex),
    BLANK(),
    'Table'[Stat Code] & " -> " & OFFSET('Table'[Stat Code], PrevRowIndex - CurrentRowIndex, BLANK())
)

[GA2]

Thank you Sahir, am i supposed to create an index first for this calculated column? I do not get the [row number] sections and they give me an error

This measurement worked however, when i'm trying to do a post if statement on the data it does not recongnize the data from the measurement. Is there a way to write this exact dax code as a calculated column?

Offset measure = CALCULATE(max('TA_EQUIP_STATUS_HISTORY'[Stat Code]), OFFSET(-1, ALLSELECTED('TA_EQUIP_STATUS_HISTORY'[EQUIP_ID],'TA_EQUIP_STATUS_HISTORY'[CREATED_DATE],'TA_EQUIP_STATUS_HISTORY'[EQUIP_STATUS_CODE],'TA_EQUIP_STATUS_HISTORY'[Stat Code],'TA_EQUIP_STATUS_HISTORY'[EQUIP_STATUS_REASON_DESC]), ORDERBY('TA_EQUIP_STATUS_HISTORY'[CREATED_DATE]),,PARTITIONBY('TA_EQUIP_STATUS_HISTORY'[EQUIP_ID])))

Thank you again so much for your help

[GA2]

Thank you! However the formula gave me the same error 

"Column 'Stat Code' in table 'TA_EQUIP_STATUS_HISTORY' cannot be found or may not be used in this expression."

Which is why i was thinking it may have something to do with the column "stat code" being a calculated column. Can I use the offset function on a calculated column? If so, maybe i'm doing something else wrong.

Appreciate you taking the time.

[amitchandak]

@GA2 , Try like

Offset meausre = CALCULATE(max(Data1[Stat Code]), OFFSET(-1, ALLSELECTED(Data1[EQUIP_ID],Data1[EQUIP_STATUS_DATE],Data1[Stat Code],Data1[EQUIP_STATUS_REASON_DESC]), ORDERBY(Data1[EQUIP_STATUS_DATE]),,PARTITIONBY(Data1[EQUIP_ID])))&""