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

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 😞