Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Finding the value difference between dates

Hi all,

 

I have a table as shown below.

 

All I want to do is, create a column which shows the difference of the parameters relative to the previous day for all units. Been browsing all over but didn't manage to find a solution. Thank you for your time

Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    maybe Johnt75 forgot to -1 for CurrentDate. like:

    Diff to prev day =

    VAR CurrentDate = 'Table'[Date]

    VAR CurrentUnit = 'Table'[Unit]

    VAR CurrentValue = 'Table'[Parameter]

    VAR PrevValue =

        LOOKUPVALUE (

            'Table'[Parameter],

            'Table'[Date], CurrentDate-1,

            'Table'[Unit], CurrentUnit,

            0

        )

    RETURN

        CurrentValue - PrevValue

7 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    You could add a column like

    Diff to prev day =
VAR CurrentDate = 'Table'[Date]
VAR CurrentUnit = 'Table'[Unit]
VAR CurrentValue = 'Table'[Parameter]
VAR PrevValue =
    LOOKUPVALUE (
        'Table'[Parameter],
        'Table'[Date], CurrentDate,
        'Table'[Unit], CurrentUnit,
        0
    )
RETURN
    CurrentValue - PrevValue
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you for your answer but it doesn't work since the assigned value for the PrevValue is same as the Current Value. Hence, the returning value is always 0. I think we should change the date in the lookupvale part to previousday, but I do not know how.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        johnt75 I created another column using NEXTDAY and assigned the date in lookupvalue to the created NEXTDAY. That way I fixed it. Thank you

    • FreemanZ's avatar
      FreemanZ
      Super User
      3 years ago

      maybe Johnt75 forgot to -1 for CurrentDate. like:

      Diff to prev day =

      VAR CurrentDate = 'Table'[Date]

      VAR CurrentUnit = 'Table'[Unit]

      VAR CurrentValue = 'Table'[Parameter]

      VAR PrevValue =

          LOOKUPVALUE (

              'Table'[Parameter],

              'Table'[Date], CurrentDate-1,

              'Table'[Unit], CurrentUnit,

              0

          )

      RETURN

          CurrentValue - PrevValue

      • FreemanZ's avatar
        FreemanZ
        Super User
        3 years ago

        hi Anonymous 

        something like this shall also work, right?

        Diff to prev day =
        VAR CurrentDate = 'Table'[Date]
        VAR CurrentUnit = 'Table'[Unit]
        VAR CurrentValue = 'Table'[Parameter]
        VAR PrevValue =
        MINX (
             FILTER(
                 'Table',
                 'Table'[Unit]=CurrentUnit
                     &&'Table'[Date]=CurrentDate-1
            ),
            'Table'[Parameter]
        RETURN
            CurrentValue - PrevValue