March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
124 | |
89 | |
85 | |
71 | |
51 |
User | Count |
---|---|
206 | |
150 | |
97 | |
78 | |
69 |