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

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