return previous value in calculated column grouping by a category

[Reda89]

Sounds simple but I have troubles solving it.

I have data Category and value, I want to make a calculated column using dax which return the results blow.

Category  value  calculated column

a             1            

a             6             1

a             1             6

b             44          

b             -9            44

b             3             -9

c              4            

c              8             4

c              9             8

[Anonymous · Accepted solution]

Hi  @Reda89 ,

I’d like to acknowledge the valuable input provided by the @Greg_Deckler  . Their initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue. 

1. create index column in power query.

2. Create calculated column.

 Column = CALCULATE ( MAX ( 'Table'[value] ), FILTER ( ALL ( 'Table' ), 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 && 'Table'[Category ] = EARLIER ( 'Table'[Category ] ) ) ) 

 

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Reda89 

See below but in your case you are going to need an Index column or something else that denotes "earlier" and "later" or basically order.

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 ) * 1.