Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
Solved! Go to Solution.
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.
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.
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
Proud to be a Super User!
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 ::
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
82 | |
79 | |
52 | |
39 | |
35 |
User | Count |
---|---|
92 | |
79 | |
51 | |
48 | |
45 |