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
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
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Since I am new to Power BI I can't tell if this should work or not, but when I tried it for my model it didn't 😞

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    FreemanZ  johnt75 Yes that worked, thank you both