Forum Discussion
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 1
a 6 1
a 1 6
b 44
b -9 44
b 3 -9
c 4
c 8 4
c 9 8
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.
2 Replies
- Greg_Deckler
Community Champion
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. 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.
