Get next non blank value per category in column

[Anonymous]

Hi all!

I'm trying to get the next value when I have a blank, but being for the same product, not just the next value in the column.

This is my table and I have a Sales column with blanks, and I want to get the "Outcome desired" column. The red values are the next ones for each product.

For Jumpers, the next values is not 30 (that would be shoes) but 50, next non value for Jumpers next month. And so forth so on.

I've been trying using if(isblank) or firstnonblankvalue, but my results are either blank or a total that doesn't make sense. Do you know how can achieve what I want?

Thank you!! 🙂

[Anonymous]

Hi @Ashish_Mathur and @DataInsights 

Thank you so much for your responses! But I'm afraid they don't work because I forgot to mention that the table is a calculated table... I'm so sorry!!

I tried to use both codes to add the column in the original table, but it doesn't give me the correct results as a summarization is being done in the virtual one, so it needs to be added later.

I'm creating a virtual table summarizing some columns and adding others, but what I showed at the beginning was a simplified version.

One of the columns I need to add within my virtual table is the column I'm struggling with.

I've tried to reuse both codes to make it work, but unfortunatly I can't... 😞

For your code @DataInsights I have issues because the first variables are columns. If I use minx or selectedvalue to get just one value, it doesn't give me the final result I want.

For your code @Ashish_Mathur I'm getting this error, I think because of the Lookupvalue function:

"A table of multiple values was supplied where a single value was expected."

Any ideas on how to do it whitin the calculated table?

This is how the code of the virtual table is going, just in case it helps!

VAR _Outlook = 2020

VAR _Actual = 2022

VAR _Sales =

                ADDCOLUMNS( SUMMARIZE('Data',

                        'Data'[Country],

                        'Data'[Product ID],

                        'Data'[Product],

                        'Data'[Date],
                       'Data'[Year],

                        'Date'[Month number],

                       "Outlook Sales", CALCULATE(SUM('Data'[Sales]), 'Data'[Year] = _Outlook),

                       "Outlook Units", CALCULATE(SUM('Data'[Units]), 'Data'[Year] = _Outlook),

                       "Actual Units", CALCULATE(SUM('Data'[Units]), 'Data'[Year] = _Actual)),

                                   "Next value Outlook Sales", XXX 

)

Thank you very much and sorry for fogetting that piece of information!! 

[Ashish_Mathur]

Hi,

Write this calculated column formula

Next sale = if(ISBLANK(Data[Sales]),LOOKUPVALUE(Data[Sales],Data[Actual date],CALCULATE(MIN(Data[Actual date]),FILTER(Data,Data[Product]=EARLIER(Data[Product])&&Data[Actual date]>EARLIER(Data[Actual date]))),Data[Product],Data[Product]),Data[Sales])

Hope this helps.

[DataInsights]

@Anonymous,

Try this calculated column:

Computed Sales = 
VAR vProduct = Table1[Product]
VAR vDate = Table1[Date]
VAR vSales = Table1[Sales]
VAR vNextDateNonBlankSalesAmount =
    MINX (
        FILTER ( Table1, Table1[Product] = vProduct && Table1[Date] > vDate ),
        Table1[Date]
    )
// use MAXX, MINX, or SUMX since only one row exists for the product/date
VAR vSalesAmountNextDate =
    MAXX (
        FILTER (
            Table1,
            Table1[Product] = vProduct
                && Table1[Date] = vNextDateNonBlankSalesAmount
        ),
        Table1[Sales]
    )
VAR vResult =
    IF ( ISBLANK ( vSales ), vSalesAmountNextDate, vSales )
RETURN
    vResult