LASTNONBLANK VALUE

[Anonymous]

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

[ERD]

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 

[Anonymous]

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

[ERD]

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

[Anonymous]

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

[ERD]

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

[Anonymous]

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

[ERD]

@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 (%)]
)