Power BI中使用DAX获取DTH_REFER_PEDID列上一条记录做IF比较的问询

To compare the current row with the previous record's DTH_REFER_PEDID value (and check if it's not equal to "A"), you'll need to correctly retrieve the prior row's data—your original linha_anterior2 variable isn't targeting the previous record, so let's fix that.

Assuming you want to:

• Partition by OVITEM_Hist (since you defined Atendido_OV as this column, meaning we're grouping by each order item)
• Order rows by DTH_REFER_PEDID to determine "previous" (adjust this if you have a separate index column instead)

Here are two reliable approaches:

Method 1: Using the INDEX Function (Modern Power BI) #

The INDEX function (available in Power BI Desktop July 2022+) makes this straightforward. It lets you reference rows relative to the current one, with partitioning and ordering:

Addition_Stats = 
VAR Atendido_OV = PR_HIST_MOVIM_PEDID[OVITEM_Hist]
-- Get the previous row's DTH_REFER_PEDID value, partitioned by OVITEM_Hist, ordered by DTH_REFER_PEDID
VAR linha_anterior = 
    INDEX(
        -1, -- Move 1 row back from current
        PR_HIST_MOVIM_PEDID, -- Table to reference
        ORDERBY(PR_HIST_MOVIM_PEDID[DTH_REFER_PEDID], ASC), -- Order by date to determine "previous"
        PARTITIONBY(PR_HIST_MOVIM_PEDID[OVITEM_Hist]) -- Group by each order item
    )[DTH_REFER_PEDID]
-- Check if previous value is not equal to "A"
VAR is_previous_not_A = NOT(linha_anterior = "A")
RETURN
    -- Integrate your existing month comparison logic here, along with the new condition
    IF(
        AND(
            PR_HIST_MOVIM_PEDID[DTH_REFER_PEDID].[Month] < PR_HIST_MOVIM_PEDID[DATA_ATENDIMENTO].[Month], -- Your original condition (assuming DAT... is DATA_ATENDIMENTO)
            is_previous_not_A -- New condition: previous row's DTH_REFER_PEDID != "A"
        ),
        -- Result if true (replace with your desired value)
        "Condition Met",
        -- Result if false (replace with your desired value)
        "Condition Not Met"
    )

Method 2: Using CALCULATE + MAX (Compatible with Older Versions) #

If you're on an older Power BI version without INDEX, use CALCULATE to find the most recent date prior to the current row, then get its DTH_REFER_PEDID value:

Addition_Stats = 
VAR Atendido_OV = PR_HIST_MOVIM_PEDID[OVITEM_Hist]
VAR current_date = PR_HIST_MOVIM_PEDID[DTH_REFER_PEDID]
-- Find the latest date before current date for the same OVITEM_Hist
VAR previous_date = 
    CALCULATE(
        MAX(PR_HIST_MOVIM_PEDID[DTH_REFER_PEDID]),
        FILTER(
            ALLEXCEPT(PR_HIST_MOVIM_PEDID, PR_HIST_MOVIM_PEDID[OVITEM_Hist]),
            PR_HIST_MOVIM_PEDID[DTH_REFER_PEDID] < current_date
        )
    )
-- Get DTH_REFER_PEDID value from that previous date
VAR linha_anterior = 
    CALCULATE(
        VALUES(PR_HIST_MOVIM_PEDID[DTH_REFER_PEDID]),
        FILTER(
            ALLEXCEPT(PR_HIST_MOVIM_PEDID, PR_HIST_MOVIM_PEDID[OVITEM_Hist]),
            PR_HIST_MOVIM_PEDID[DTH_REFER_PEDID] = previous_date
        )
    )
VAR is_previous_not_A = NOT(linha_anterior = "A")
RETURN
    IF(
        AND(
            PR_HIST_MOVIM_PEDID[DTH_REFER_PEDID].[Month] < PR_HIST_MOVIM_PEDID[DATA_ATENDIMENTO].[Month],
            is_previous_not_A
        ),
        "Condition Met",
        "Condition Not Met"
    )

Key Notes: #

• Replace "Condition Met" and "Condition Not Met" with the actual results you want when the condition is satisfied or not.
• If you're using a separate index column (instead of DTH_REFER_PEDID) to determine row order, adjust the ORDERBY (Method 1) or filter logic (Method 2) to use that index instead of the date.
• The ALLEXCEPT function ensures we only look at rows within the same OVITEM_Hist group—remove this if you don't need to partition by order item.

内容的提问来源于stack exchange，提问作者Jefferson Souza