Add new column with next non blank value

[Andregewehr]

Hi everyone, 

I have this table:

I want to add a new column filling the blank values with the next value for each SKU (A, B and C) and the next date. I tried this DAX formula:

FIRSTNONBLANK = IF(ISBLANK('Table'[Stock]) || 'Table'[Stock] = "",

CALCULATE(

FIRSTNONBLANK('Table'[Stock], TRUE()),

FILTER('Table',

'Table'[SKU] = EARLIER('Table'[SKU]) && 'Table'[Date] <= EARLIER('Table'[Date]) && 'Table'[Stock] <> "")

),

[Stock]

)

These are the expected values for each SKU (A, B and C) and the FIRSTNONBLANK errors I put in red. All the values for B and C are zero. Where is my mistake? 😥

[FreemanZ · Accepted solution]

hi @Andregewehr 

try like:

Column =

    MAXX(

        TOPN(

             1,

             FILTER(

                'Table',

                'Table'[SKU] = EARLIER('Table'[SKU]) && 'Table'[Date] <= EARLIER('Table'[Date]) && 'Table'[Stock] <>BLANK()

              ),

              'Table'[Date]

         ),

         'Table'[Stock]

    )

[Andregewehr]

Oh my god, that was perfect! Thank you!