cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Fill in blanks with last non blank value (using DAX measure)

Hi guys,

 

Does anyone know how to fill in blank values with the previous non blank value? I need this to be done with a dax measure because of my data model, as I'm using a date dimensional table.

The measure I'm currently using is this one:

PrecoMedioMovelv2 = 

CALCULATE(
    LASTNONBLANK(MaterialAvaliacao[PrecoMedioMovel];MaterialAvaliacao[PrecoMedioMovel]);
    FILTER(ALL('Date'); 'Date'[Date] <= MAX('Date'[Date]))
)


But the problem is that it always returns the max value of the column MaterialAvaliacao[PrecoMedioMovel] for the given date context, as you can see in the picture below:

 

pbi.png




 

 

 

 

 

 

 

 

 

 

 

 

 

 

The desired output is the following:

 

desiredoutput.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Here's also the data model I'm using (for the current exercice, table MaterialMovimento doesn't need to be considered)

dmpbi.png

 

 

 

 

 

 

 

 

 

 

 

Thanks in advance!! 

1 ACCEPTED SOLUTION

Hi @Anonymous ,
Here is the pbix.  Glad it worked! PBIX FILE 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel





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

Proud to be a Super User!




View solution in original post

11 REPLIES 11
Nathaniel_C
Super User
Super User

Hello @Anonymous ,
Try this:

PrecoMedioMovelv2 =
VAR _date =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        LASTNONBLANK (
            MaterialAvaliacao[PrecoMedioMovel];
            MaterialAvaliacao[PrecoMedioMovel]
        );
        FILTER ( ALLEXCEPT ( 'Date', 'Date'[Date] ); 'Date'[Date] <= _date )
    )

 This should get the date from the row that you are on.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel 





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

Proud to be a Super User!




Anonymous
Not applicable

Thanks for the quick answer.

But that measure returns the following error message: 'A single value for column 'Date' in table 'Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'

Sorry try this.

PrecoMedioMovelv2 =
VAR _date =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        LASTNONBLANK (
            MaterialAvaliacao[PrecoMedioMovel],
            MaterialAvaliacao[PrecoMedioMovel]
        ),
        FILTER ( ALLEXCEPT ( 'Date', 'Date'[Date] ), MAX ( 'Date'[Date] ) <= _date )
    )

 





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

Proud to be a Super User!




Anonymous
Not applicable

It's returning 6,15 for every record.. 

In case you want to try, here's a link with the .pbix file: https://drive.google.com/drive/folders/1xtRMllYdA3HYsF0dzDa-UmnRTloDh6Ff?usp=sharing

Thanks!

Will do.





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

Proud to be a Super User!




Hi @Anonymous ,
Try this: lno.PNG

 




LastNonBlank Value =
VAR LastNonBlankDate =
    CALCULATE (
        MAX ( myTable[Date] ),
        FILTER (
            ALL ( myTable ),
            myTable[Date] <= MAX ( myTable[Date] )
                && myTable[Value] <> 0
        )
    )
RETURN
    CALCULATE (
        SUM ( myTable[Value] ),
        FILTER ( ALL ( myTable ), myTable[Date] = LastNonBlankDate )
    )



Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

 





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

Proud to be a Super User!




Hi @Anonymous ,
Here is the pbix.  Glad it worked! PBIX FILE 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel





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

Proud to be a Super User!




How can i use the same logic but if i have an aditional column called "product"?

 

I want the same but taking in consideration the differents products.

 

Eg: 

Gustavo98_0-1668782409020.png

 

   

Dear @Nathaniel_C , thank you so much to share this solution. Worked very well for me.

Anonymous
Not applicable

Hi, 

 

what if the column "value" isn't a number/integer/etc but text? 

good to know.. my example is a sorted file. 

Anonymous
Not applicable

Hi @Nathaniel_C 

 

Sorry I know this is from a while ago but I just wondered, I am trying to do the same thing but with a text value therefore SUM cannot be used. Can I ask how you would chnge this to work for a STRING data type?

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors