Forum Discussion

Tan_LC's avatar
Tan_LC
Icon for Helper II rankHelper II
3 years ago

Fill down based on multiple conditions

Hi,

 

I've a set of data as belowand want to do Fill Down on column Qty using DAX based on 2 conditions: Item and Year.

Please refer Desired Result column for the wanted outcome.

 

ItemYearQtyDesired Result
A2019  
A2020100100
A2021 100
A20227575
A2023 75
B20196565
B2020 65
B2021 65
B20228080
B2023 80
C2019  
C2020  
C20213030
C2022 30
C20232020

 

 

Thanks.

Regards,

LC

1 Reply

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Tan_LC it was really challenging to create this solution as it is not simple previous row data.

    Please create  new column with code as below. When I check it with desired result difference is zero.

    Did I answer your question? Mark my post as a solution! Kudos Appreciated!

     

    Testing =
    VAR __current_item = Sheet2[Item]
    VAR __current_year = Sheet2[Year]
    VAR __previous_row =
        CALCULATE(
            MAX(Sheet2[Year]),
            FILTER(
                ALL(Sheet2),
                Sheet2[Item] = __current_item &&
                Sheet2[Year] < __current_year &&
                NOT(ISBLANK(Sheet2[Qty]))
            )
        )
    VAR __previous_rowQty =
        LOOKUPVALUE(
            Sheet2[Qty],
            Sheet2[Item], __current_item,
            Sheet2[Year], __previous_row
        )
    RETURN
            IF(
            NOT(ISBLANK(Sheet2[Qty])),
            Sheet2[Qty],
            IF(ISBLANK(__previous_rowQty), BLANK(), __previous_rowQty)
        )