Forum Discussion
Shift Data by one row down
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:
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.
3 Replies
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 ::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
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.
