Help Dax

[JunMenoza]

Hi , How would I add previous transaction in the Prev_trans columm

[Greg_Deckler · Accepted solution]

@JunMenoza You need a variation on MTBF. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[Whitewater100 · Accepted solution]

Hi:

If you can put the date in the table e.g. 4-10-2021  and add an index column using Power Query (1 -8) then this calculated column will work for you.

Previous Value 2 =

CALCULATE(

LASTNONBLANKVALUE(Data[Date], SUM(Data[Mileage])),

ALLEXCEPT(Data, Data[Sales Rep]),

Data[Index]<EARLIER(Data[Index]) &&

Data[Day]

)

Here is a picture of the results:(I named your table Data)

To put an index column in, go to transform data, "add index column (from 1). Here is an example file. I hope this solves your question!

https://drive.google.com/file/d/1NmKBreErOFNw8EqCo3yHMvItL_p7QZkE/view?usp=sharing 

[Greg_Deckler · Accepted solution]

@JunMenoza You need a variation on MTBF. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous