Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Previous value calculated column

Hello all, 

 

I hope to get a help on getting a correct previous value of the following table:

 

I'm using this formula.

I've tried some measures but somewhow they worked as measure but not as calculated column.

apreciated your help.

 

here the pbi file https://we.tl/t-rfxvA0vqS3

 

thanks

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    Please try:

    Previous Value = 
VAR _Cur_Date = 'Sheet1'[Date]
VAR _Pre_Date =
    CALCULATE (
        MAX ( 'Sheet1'[Date] ),
        FILTER ( ALL ( 'Sheet1' ), 'Sheet1'[Date] < _Cur_Date )
    )
VAR _Pre_Value =
    CALCULATE (
        MAX ( 'Sheet1'[Real h_M] ),
        FILTER ( ALL ( 'Sheet1' ), 'Sheet1'[Date] = _Pre_Date )
    )
RETURN
    _Pre_Value

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    Please try:

    Previous Value = 
VAR _Cur_Date = 'Sheet1'[Date]
VAR _Pre_Date =
    CALCULATE (
        MAX ( 'Sheet1'[Date] ),
        FILTER ( ALL ( 'Sheet1' ), 'Sheet1'[Date] < _Cur_Date )
    )
VAR _Pre_Value =
    CALCULATE (
        MAX ( 'Sheet1'[Real h_M] ),
        FILTER ( ALL ( 'Sheet1' ), 'Sheet1'[Date] = _Pre_Date )
    )
RETURN
    _Pre_Value

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Anonymous , 

       

      Thanks for the solution, it works for what I want 🙂

      however, I did some stress tests with repetetive days and it give wrong values.

       

       

      any solution to avoid this?

       

      thanks anyway for your help 🙂

       

      regards, 

      Abdel.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Anonymous ,

        The problem is that the date column is not unique. Try to add an index column to the Power Query Eidtor. And use like:

        Previous Value =
VAR _Cur_Index = 'Sheet1'[Index]
VAR _Pre_Index =
    CALCULATE (
        MAX ( 'Sheet1'[Index] ),
        FILTER ( ALL ( 'Sheet1' ), 'Sheet1'[Index] < _Cur_Index )
    )
VAR _Pre_Value =
    CALCULATE (
        MAX ( 'Sheet1'[Real h_M] ),
        FILTER ( ALL ( 'Sheet1' ), 'Sheet1'[Index] = _Pre_Index )
    )
RETURN
    _Pre_Value

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly -- How to provide sample data