Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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])
Hi @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
@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-5c324...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |