Fill down based on multiple conditions

Tan_LC — Thu, 17 Aug 2023 07:20:07 GMT

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.

Thanks.

Regards,

Re: Fill down based on multiple conditions

some_bih — Thu, 17 Aug 2023 13:37:49 GMT

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)

)