Fill calculated column and replace null value with previous values of same Year

[carocaro81]

Hello All,

I need your help with DAX functions please. I’m looking for a solution for the below case:

I have to fill the column [ForecastAmount] (for each combination [Article-ID] & [Customer-ID] & [Time-ID]).

 In case the [SalesAmount] for a row was 0 (for each combination [Article-ID] & [Customer-ID] & [Time-ID]), I have to take the [SalesAmount] (not equals to ZERO or not NULL) of the last previous [Time-ID] available (for the same combination [Article-ID] and [Customer-ID]).

Also, the last previous [Time-ID] should be in the same year.

[ForcastAmount] is a calculated column.

I’m trying to solve this problem in Tabular Model in Visual Studio.

Many Thanks for your answers and guidance.

Caro

[v-shex-msft · Accepted solution]

HI @carocaro81,

You can try to use below measure to get recently non-blank sales amount:

Forcat Sales =
VAR Previous_Non_Zero_ID =
    MAXX (
        FILTER (
            ALL ( Table ),
            Table[Article-ID] = MAX ( Table[Article-ID] )
                && Table[Customer-ID] = MAX ( Table[Customer-ID] )
                && Table[Time-ID] < MAX ( Table[Time-ID] )
                && Table[SalesAmount] <> 0
        ),
        Table[Time-ID]
    )
RETURN
    IF (
        MAX ( Table[SalesAmount] ) = 0,
        LOOKUPVALUE (
            Table[SalesAmount],
            Table[Article-ID], MAX ( Table[Article-ID] ),
            Table[Customer-ID], MAX ( Table[Customer-ID] ),
            Table[Time-ID], Previous_Non_Zero_ID
        ),
        MAX ( Table[SalesAmount] )
    )

Regards,

Xiaoxin Sheng