Previous Value Using DAX

[Anonymous]

[edhans · Accepted solution]

EDIT: Better way with no index, but I'm leaving my previous reply there. The Index method would allow you to move up/down by any number of rows you wish. Below will only move up to the previous date, but doesn't require an index to do this.
Use this calculated column (final edit - got rid of EARLIER altogether - Variables are better - didn't upload new image, so you can see the earlier EARLIER formula):

Previous 2 = 
VAR CurrentDate = Data[Date]
VAR PreviousDate =
    CALCULATE(
        LASTDATE(Data[Date]),
        FILTER(Data,
        Data[Date] < CurrentDate
        )
    )
RETURN
CALCULATE(
    MAX(Data[Users]),
    FILTER(
        Data,
        Data[Date] = PreviousDate
    )
)

Index method:

If you can create an Index column in Power Query this will help.

1. In Power Query, sort your date column ascending
2. On the Add Columns tab, click on Add Index Column. Doesn't matter if it starts with 0 or 1.
3. Close and load the table.

Then Add this custom column in the table:

Previous = 
CALCULATE(
    MAX(Data[Users]),
    FILTER(
        Data,
        Data[Index] = EARLIER(Data[Index],1) -1
    )
)

You'll have this:

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

First, add a [index] column in Edit Queries;

Second, create the measure:

Measure 4 = 
VAR x = 
CALCULATE(
    MIN(Sheet2[Users]),
    FILTER(
        ALLSELECTED(Sheet2),
        Sheet2[Date] < MAX(Sheet2[Date]) && Sheet2[Index] = MAX(Sheet2[Index]) + 1
    )
)
RETURN
IF(
    x = BLANK(),
    0,
    x
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans · Accepted solution]

EDIT: Better way with no index, but I'm leaving my previous reply there. The Index method would allow you to move up/down by any number of rows you wish. Below will only move up to the previous date, but doesn't require an index to do this.
Use this calculated column (final edit - got rid of EARLIER altogether - Variables are better - didn't upload new image, so you can see the earlier EARLIER formula):

Previous 2 = 
VAR CurrentDate = Data[Date]
VAR PreviousDate =
    CALCULATE(
        LASTDATE(Data[Date]),
        FILTER(Data,
        Data[Date] < CurrentDate
        )
    )
RETURN
CALCULATE(
    MAX(Data[Users]),
    FILTER(
        Data,
        Data[Date] = PreviousDate
    )
)

Index method:

If you can create an Index column in Power Query this will help.

1. In Power Query, sort your date column ascending
2. On the Add Columns tab, click on Add Index Column. Doesn't matter if it starts with 0 or 1.
3. Close and load the table.

Then Add this custom column in the table:

Previous = 
CALCULATE(
    MAX(Data[Users]),
    FILTER(
        Data,
        Data[Index] = EARLIER(Data[Index],1) -1
    )
)

You'll have this: