topic Re: DAX Row minus Previous Row in DAX Commands and Tips

DAX Row minus Previous Row

archerjayden — Fri, 31 Jul 2020 04:21:57 GMT

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

Re: DAX Row minus Previous Row

amitchandak — Fri, 31 Jul 2020 04:58:20 GMT

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])

Re: DAX Row minus Previous Row

archerjayden — Fri, 31 Jul 2020 08:38:04 GMT

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

Re: DAX Row minus Previous Row

Fowmy — Fri, 31 Jul 2020 09:27:47 GMT

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 🙂

YouTube, LinkedIn

Re: DAX Row minus Previous Row

amitchandak — Tue, 10 May 2022 15:42:59 GMT

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