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 la...
Need Help
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
    )
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

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