Finding the value difference between dates

[Anonymous]

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

[johnt75]

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 · Accepted solution]

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

[Anonymous]

@FreemanZ  @johnt75 Yes that worked, thank you both

[FreemanZ]

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]

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]

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]

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