Fill down based on multiple conditions

[Tan_LC]

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.

Item	Year	Qty	Desired Result
A	2019
A	2020	100	100
A	2021		100
A	2022	75	75
A	2023		75
B	2019	65	65
B	2020		65
B	2021		65
B	2022	80	80
B	2023		80
C	2019
C	2020
C	2021	30	30
C	2022		30
C	2023	20	20

Thanks.

Regards,

LC

[some_bih]

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)

    )