How to reference previous values from the same column where the DAX program/code is being written?

[Commons94]

Dear Community,

I have a problem facing me since the past few months. I have multiple conditions (mentioned below) to develop 2 result columns - "Result Date" & "Result Qty". The problem I'm facing is that in some of the conditions, the result has to be the the same result as in the previous rows of the same column where the DAX code is being written. I'm currently performing it in Excel that's why I'm using "XLOOKUP" function. If you take a closer at the XLOOKUP formula, you can see that the result part of the formula references the "Result Date"/"Result Qty" columns which are the columns where the results are printed.

Any suggestions on how this could be done with Power BI would be helpful, as the dataset is large and performing these calculations in Excel doesn't make much sense now.

Thanks in advance!

Conditions for the 1st results column - "Result Date"

Result Date:

• IF "Previous ID" =""
• Then ""
• IF "SL Type" = "First SL"
• Then "Date"
• IF "SL Type" = "Unconfirmed"
• Then XLOOKUP("Previous ID", "Current ID", "Result Date","")
• IF "SL Type" = "Confirmed" && "Check">1
• Then "Date"
• IF "SL Type" = "Confirmed" && Check =1 &&
  • Then "Date"
  • Then XLOOKUP("Previous ID", "Current ID", "Result Date","")
• IF "Change Required?" = "Change date" || "Change Required" = "Change both"
• IF "Change Required?" = "Change Qty" || "Change Required" = "no"
• Else ""

Conditions for the 2nd results column - "Result Qty"

Result Qty:

• IF "Previous ID" =""
• Then ""
• IF "SL Type" = "First SL"
• Then "Qty"
• IF "SL Type" = "Unconfirmed"
• Then XLOOKUP("Previous ID", "Current ID", "Result Qty","")
• IF "SL Type" = "Confirmed" && "Check">1
• Then "Qty"
• IF "SL Type" = "Confirmed" && Check =1 &&
• IF "Change Required?" = "Change Qty" && "Qty decrease?" = "yes" 
• Then "Qty"
• IF "Change Required?" = "Change Qty" && "Qty increase?" = "yes"
• Then XLOOKUP("Previous ID", "Current ID", "Result Qty","")
• IF "Change Required?" = "Change both" && "Qty decrease?" = "yes" 
• Then "Qty"
• IF "Change Required?" = "Change both" && "Qty increase?" = "yes"
• Then XLOOKUP("Previous ID", "Current ID", "Result Qty","")
• IF "Change Required?" = "Change Date" || "Change Required" = "no"
• Then XLOOKUP("Previous ID", "Current ID", "Result Qty","")
• Else ""

[Commons94]

Hi @v-yiruan-msft 

Thanks once again for your reply. below you find the expected results, in the "Comment" column you find the reason why these are the expected results. Please take a closer on the ID "5337836_10_037/13", here with the existing formula gives a blank because the lookup value of previous ID is also blank (******/13 has been confirmed 1st time in the sequence that's why).

[v-yiruan-msft]

Hi @Commons94 ,

I updated the sample pbix file, please check if that is what you want. Any comment or problem, please feel free to let me know.

Best Regards

[Commons94]

Hello @v-yiruan-msft ,

thanks a lot for your reply and dax-program. It seems to work correctly for the given sample dataset but when I apply it to the complete dataset I don't get the some results where "SL Type" = "Confirmed". Can you please provide your suggestion on how the code can be altered for the following sample dataset? thanks a lot... 

[v-yiruan-msft]

Hi @Commons94 ,

Could you please provide your expected [Result Date] and [Result Qty] base on above sample data when "SL Type" = "Confirmed"? Thank you.

Best Regards

[Commons94]

 Sample

[v-yiruan-msft]

Hi @Commons94 ,

I create a sample pbix file(see the attachment), please check if that is what you want.

1. Create two calculated columns as below to get the qty and date for the previous ID

Previous ID Date = 
VAR _pid = 'Table'[Previous ID]
VAR _rdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( 'Table', 'Table'[Current ID] = _pid )
    )
RETURN
    _rdate

Previous ID Qty = 
VAR _pid = 'Table'[Previous ID]
VAR _rQty =
    CALCULATE (
        MAX ( 'Table'[Qty] ),
        FILTER ( 'Table', 'Table'[Current ID] = _pid )
    )
RETURN
    _rQty

2. Create two calculated column as below to get the result data and result qty

Result Date = 
IF (
    ISBLANK ( [Previous ID] ),
    BLANK (),
    SWITCH (
        TRUE (),
        [SL Type] = "First SL", [Date],
        [SL Type] = "Unconfirmed", [Previous ID Date],
        [SL Type] = "Confirmed"
            && [Check] > 1, [Date],
        [SL Type] = "Confirmed"
            && [Check] = 1
            && [Change Required?] IN { "Change date", "Change both" }, [Date],
        [SL Type] = "Confirmed"
            && [Check] = 1
            && [Change Required?] IN { "Change Qty", "no" }, [Previous ID Date]
    )
)

Result Qty = 
IF (
    ISBLANK ( [Previous ID] ),
    BLANK (),
    SWITCH (
        TRUE (),
        [SL Type] = "First SL", [Qty],
        [SL Type] = "Unconfirmed", [Previous ID Qty],
        [SL Type] = "Confirmed"
            && [Check] > 1, [Qty],
        [SL Type] = "Confirmed"
            && [Check] = 1
            && [Change Required?]
            IN { "Change Qty", "Change both" }
                && 'Table'[Qty decrease?] = "yes", [Qty],
        [SL Type] = "Confirmed"
            && [Check] = 1
            && ( ( [Change Required?]
            IN { "Change Qty", "Change both" }
                && 'Table'[Qty Increase?] = "yes" )
            || 'Table'[Change Required?] IN { "Change Date", "no" } ), [Previous ID Qty]
    )
)

Best Regards