Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Find the last non blank value

Hi All,

 

I have data like below.  This is the data just for one store. If the data is not present for a particular month, I want to take the last month's data.

 

For example: For the Sep 2019, there is no data, hence I want to see 23.1%(Aug data).

 

Please help.

 

Thanks,

Bhavya

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi, Anonymous 

    You can first create a index column and use this formula to create a calculate column.

     

     

    Lastmonth if blank =
VAR currentIndex = Sheet1[Index]
RETURN
    IF (
        ISBLANK ( [Ave Distribution%] ),
        CALCULATE (
            MAX ( Sheet1[Ave Distribution%] ),
            FILTER ( Sheet1, Sheet1[Index] = currentIndex - 1 )
        ),
        [Ave Distribution%]
    )

     

     

    Best,
    Paul

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi, Anonymous 

    You can first create a index column and use this formula to create a calculate column.

     

     

    Lastmonth if blank =
VAR currentIndex = Sheet1[Index]
RETURN
    IF (
        ISBLANK ( [Ave Distribution%] ),
        CALCULATE (
            MAX ( Sheet1[Ave Distribution%] ),
            FILTER ( Sheet1, Sheet1[Index] = currentIndex - 1 )
        ),
        [Ave Distribution%]
    )

     

     

    Best,
    Paul

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.