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

[Anonymous]

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:

The desired output is the following:

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

Thanks in advance!! 

[Nathaniel_C]

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 

[Anonymous]

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.'

[Nathaniel_C]

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 )
    )

[Anonymous]

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!

[Nathaniel_C]

Will do.

[Nathaniel_C]

Hi @Anonymous ,
Try this: 

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

 

[Nathaniel_C · 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

[Gustavo98]

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: 

[Anonymous]

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

[Anonymous]

Hi, 

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

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

[Anonymous]

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?