Forum Discussion

Reda89's avatar
Reda89
Regular Visitor
2 years ago
Solved

return previous value in calculated column grouping by a category

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      ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

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/339586.
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.