Forum Discussion
Helper IDAX Row minus Previous Row
Hi,
I am looking for help on achieving moving range from data.
How do I get DAX to subtract row by previous row?
I am working on SQL Server direct query mode and unable to crack this for my report.
Please advise and kindly refer to excel sample data and formula below I’ve tried.
[TotalQty] =
Divide(SUM([delivered_quantity]),SUM([req_quantity]),0)
PreviousRowSubtract =
([TotalQty]) - CALCULATE (
SUMX([delivered_quantity])/SUMX([req_quantity])*100,FILTER([Date]=dateadd([Date],-1,Day)))
Column D is what I am trying to achieve this would have other filter contexts like Year, MonthNum, WeekNum and Weekday in the form of Slicers
Many thanks
Archer
Sample Data
4 Replies
- amitchandak
Super User
archerjayden , try as new column
new colum =
var _date = maxx( filter(Table,[date] <earlier([date])),[Date])
return
[total qty] - maxx( filter(Table,[date] = _date)),[total qty])//prefer this if you have continuous date
new colum =
[total qty] - maxx( filter(Table,[date] = earlier([date])-1),[total qty])
archerjaydenHi amitchandak thanks for your approach! I am using Direct query and function Earlier doesnt work no matter how I tried? would you happen to know an alternative approach for the same please?
PS : I can't even add an Index column from PQ
- amitchandak
archerjayden , You have to day on day time intelligence using date table (or prefer date table)
You can use previousday function
sum(Table[Total Qty]) - calculate(Table[Total Qty]) ,previousday ('Date'[Date] ) )
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9
- Fowmy
archerjayden
This could work:Column = VAR _DATE = 'Table'[A] VAR _LASTDATE = CALCULATE( MAX('Table'[A]), FILTER('Table','Table'[A] < _DATE) ) RETURN [B] - CALCULATE( MAX([C]),FILTER('Table','Table'[A] = _LASTDATE))________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
