Forum Discussion

carocaro81's avatar
carocaro81
New Member
8 years ago
Solved

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

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

 

  • Anonymous's avatar
    Anonymous
    8 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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