Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Get next non blank value per category in column

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.

 

alicia_mtz_0-1708094818406.png

 

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!! 🙂

3 REPLIES 3
Anonymous
Not applicable

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
Super User
Super User

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.

Ashish_Mathur_0-1708215651360.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
DataInsights
Super User
Super User

@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

 

DataInsights_0-1708187135440.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.