Forum Discussion

Commons94's avatar
Commons94
Frequent Visitor
2 years ago

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

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 faci...
@Dax
@Dax help
Commons94's avatar
Commons94
Frequent Visitor
2 years ago

 Sample

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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