DAX Row minus Previous Row

[archerjayden]

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

[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 🙂

YouTube, LinkedIn

[amitchandak]

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

[archerjayden]

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

[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-5c324...