Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

LASTNONBLANK VALUE

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

 

Need Help

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        ERD
        Icon for Community Champion rankCommunity Champion
        5 years ago

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