Forum Discussion
Tan_LC
Helper II
Helper IIFill 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. Item Year...
some_bih
Community Champion
Community ChampionHi 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)
)