topic Re: LASTNONBLANK VALUE in DAX Commands and Tips

LASTNONBLANK VALUE

Anonymous — Wed, 17 Mar 2021 16:58:58 GMT

Hello everyone,

I am writing to you about a measurement query that I can't solve.
Below is a table of yield calculation.

I would like to be able to display in the column "Rend_N-1(%)" the last value filled in the column "Rend (%)".
Example for the 21/12/2019 I would like "Rend_N-1 (%)" to be equal to 77.90% which corresponds to the "Rend (%)" of 03/12/2019.
Second example : For the 03/12/2019 I would like "Yield_N-1 (%)" to be equal to 76.72% which corresponds to the "Yield (%)" of the 12/11/2019.

I thought of using the "LASTNONBLANK" function but I still can't do it...

I also attach a PBIX to understand better : https://1drv.ms/u/s!Ao1OrcTeY008gYVQjq14qaCDLHAb6w?e=Jk3p2l

Thank you in advance for your help,

Joël

Re: LASTNONBLANK VALUE

ERD — Mon, 22 Mar 2021 10:12:32 GMT

Hello Anonymous ,

According to your examples, you just need to find the previous value of "Rend (%)" column.

If you are using calculated columns, then the solution can be found in this forum:

https://community.powerbi.com/t5/Desktop/Get-previous-row-value-in-new-column/td-p/496182

Re: LASTNONBLANK VALUE

Anonymous — Mon, 22 Mar 2021 10:38:25 GMT

Hello ERD

Thank you for your help first of all. And to answer your question: no "Rend (%) is not a calculated column, it is a measure. I consulted your link and unfortunately it does not answer my problem. Any other ideas ?

Thanks in advance,

Joël

Re: LASTNONBLANK VALUE

ERD — Mon, 22 Mar 2021 13:00:22 GMT

Anonymous ,

In this case here is an option for a measure:

Rend_N-1 (%) measure = VAR currentDate = SELECTEDVALUE(YourTableName[Date]) VAR previousDate = CALCULATE( MAX(YourTableName[Date]), FILTER( ALLSELECTED(YourTableName), YourTableName[Date] < currentDate) ) RETURN CALCULATE( MAX(YourTableName[Rend (%)]), FILTER( ALLSELECTED(YourTableName), YourTableName[Date] = previousDate ) )

Re: LASTNONBLANK VALUE

Anonymous — Tue, 23 Mar 2021 07:54:48 GMT

Thank you for your help ERD it is very generous of you. I just tried your formula but I don't understand very well in your formula or is it that I fill "Rend (%)" with a "MAX" function since "Rend %" is a measure and not a column and "Max" only works with column names it seems to me. I used the same code but with the "MAXX" function which can work with expressions but I have this error message that came up "A "FILTER" function was used in a True/False expression used as a table filter expression. This is not allowed." so no result either...

Did you try running your formula in the PBIX I made available?

Thanks in advance,

Joel

Re: LASTNONBLANK VALUE

ERD — Tue, 23 Mar 2021 15:17:06 GMT

Anonymous ,

I'm afraid I cannot download files, but to make MAXX work you can play with your current measure and try to use it in the MAXX function in the next way:

Rend_N-1 (%) measure = VAR currentDate = SELECTEDVALUE(YourTableName[Date]) VAR previousDate = CALCULATE( MAX(YourTableName[Date]), FILTER( ALLSELECTED(YourTableName), YourTableName[Date] < currentDate) ) RETURN MAXX( FILTER( ALLSELECTED(YourTableName), YourTableName[Date] = previousDate ), [your_measure] )

Re: LASTNONBLANK VALUE

Anonymous — Tue, 23 Mar 2021 17:42:05 GMT

Thanks ERD but I have the same error message : "A "FILTER" function was used in a True/False expression used as a table filter expression. This is not allowed." when I used the "filter" function ...

Re: LASTNONBLANK VALUE

ERD — Fri, 02 Apr 2021 10:21:54 GMT

Anonymous,
Just in case, please, update your PBI Desktop.

Rend_N-1 (%) measure = VAR currentDate = SELECTEDVALUE(dim_date[Date]) VAR previousDate = CALCULATE( MAX(dim_date[Date]), FILTER( ALLSELECTED(dim_date), dim_date[Date] < currentDate) ) RETURN MAXX( FILTER( ALLSELECTED(dim_date), dim_date[Date] = previousDate ), [Rend_dynamique (%)] )