topic Re: Fill in blanks with last non blank value (using DAX measure) in DAX Commands and Tips

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

Anonymous — Thu, 28 Nov 2019 00:26:01 GMT

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

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

Nathaniel_C — Thu, 28 Nov 2019 00:45:25 GMT

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

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

Anonymous — Thu, 28 Nov 2019 00:55:17 GMT

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

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

Nathaniel_C — Thu, 28 Nov 2019 01:02:10 GMT

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

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

Anonymous — Thu, 28 Nov 2019 01:14:02 GMT

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!

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

Nathaniel_C — Thu, 28 Nov 2019 01:18:42 GMT

Will do.

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

Nathaniel_C — Thu, 28 Nov 2019 21:37:24 GMT

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

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

Nathaniel_C — Wed, 04 Dec 2019 22:56:14 GMT

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

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

Anonymous — Thu, 23 Jul 2020 11:20:04 GMT

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?

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

Anonymous — Thu, 10 Sep 2020 13:08:39 GMT

Hi,

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

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

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

Anonymous — Fri, 18 Nov 2022 11:20:35 GMT

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

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

Anonymous — Fri, 18 Nov 2022 14:40:21 GMT

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:

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

dribblekeo — Thu, 23 Jan 2025 06:23:16 GMT

Thank you for your information .
Note :
If we cannot apply the

   && myTable[Value] <> 0

in measure the logic will be changed .