Shift Data by one row down

[lightsaberluke]

Hi, I am trying to create a new measure where I have my data from another row just shifted down one. I found a video that used the following formula but it did not work: 

Previous Year = Calculate (distinctcount('Sheet1' [Order ID]), DATEADD(Sheet1' [Order Date].[Date], -1, YEAR))

What I have: 

What I expect: 

[Anonymous · Accepted solution]

Hi @lightsaberluke 

Please try this:

Here's a sample:

Table:

Then add an index column in powerquery:

Next add a measure:

MEASURE =
VAR _currentIndex =
    MAX ( 'Table'[Index] )
VAR _previousIndex =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] < _currentIndex )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] = _previousIndex )
    )

The result is as follow:

Or if you have an date column:

You can try this measure without an index column:

MEASURE 2 =
VAR _currentDate =
    MAX ( 'Table'[Date] )
VAR _PreviousDate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] < _currentDate )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _PreviousDate )
    )

 The result is as follow:

Best Regards

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

[ryan_mayu]

 @lightsaberluke 

you need to create a datetable. 

The column for the first parameter is from date table , not the fact table.

= DATEADD(DateTime[DateKey],-1,year)

https://learn.microsoft.com/en-us/dax/dateadd-function-dax?wt.mc_id=DP-MVP-5004616

[d_rohlfs]

Hi @lightsaberluke, 
I think there might be a way to do this in DAX, but I have found it much easier to do this in Power Query. Follow along with this blog, but just reverse the column that you choose in the merge (instead of From 1 to From 0, do From 0 to From 1).
Power BI – Reference a Previous Row in Power Query : :: Welcome To EPM Strategy ::