Forum Discussion
Helper IPrevious value with group/category
Hello all,
I have values for 3 items against date. I want to calculate the rate of change of the value for the item.The dates are not necessarily consecutives and each item may not have the same dates. If there was no item, I can use index to track the previous value. However with items I am lost. I want my data as follows.
| Date | Item | Value | Return (prev value - current) |
| 11/12/2020 | A | 1 | |
| 11/15/2020 | A | 2 | 1 |
| 11/16/2020 | A | 3 | 0.5 |
| 11/17/2020 | A | 2 | -0.333333333 |
| 11/18/2020 | A | 1 | -0.5 |
| 11/19/2020 | A | 2 | 1 |
| 11/21/2020 | A | 1 | -0.5 |
| 11/22/2020 | A | 4 | 3 |
| 11/26/2020 | A | 5 | 0.25 |
| 11/12/2020 | B | 12 | |
| 11/15/2020 | B | 11 | -0.083333333 |
| 11/16/2020 | B | 12 | 0.090909091 |
| 11/17/2020 | B | 3 | -0.75 |
| 11/18/2020 | B | 21 | 6 |
| 11/19/2020 | B | 1 | -0.952380952 |
| 11/21/2020 | B | 2 | 1 |
| 11/22/2020 | B | 4 | 1 |
| 11/26/2020 | B | 6 | 0.5 |
| 11/12/2020 | C | 7 | |
| 11/15/2020 | C | 1 | -0.857142857 |
| 11/16/2020 | C | 2 | 1 |
| 11/17/2020 | C | 3 | 0.5 |
| 11/18/2020 | C | 5 | 0.666666667 |
| 11/19/2020 | C | 6 | 0.2 |
| 11/21/2020 | C | 7 | 0.166666667 |
| 11/22/2020 | C | 8 | 0.142857143 |
| 11/26/2020 | C | 6 | -0.25 |
Thank you.
sonm10 - Sorry, I messed that up.
Column = VAR __Current = [Value] VAR __PreviousDate = MAXX(FILTER('Test','Test'[Date] < EARLIER('Test'[Date]) && [Group] = EARLIER([Group])),[Date]) VAR __Previous = MAXX(FILTER('Test','Test'[Date] = __PreviousDate && [Group] = EARLIER([Group])),[Value]) RETURN __Current - __Previous
6 Replies
- Greg_Deckler
Community Champion
sonm10 - If I understand what you are asking, 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 __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date]) && [Item] = EARLIER([Item])),[Value])
RETURN
__Current - __Previous
sonm10Hello Greg_Deckler. I have implemented your solution but few data are coming right but most of them are incorrect. Attached below the screenshot of the sample dataset.
I have calcualted the new column as follows:
Column =
VAR __Current = [Value]
VAR __Previous = MAXX(FILTER('Test','Test'[Date] < EARLIER('Test'[Date]) && [Group] = EARLIER([Group])),[Value])
RETURN
__Current - __Previous
The return field is what should be the correct value. Its been calculated in excel for comparisons.- Greg_Deckler
sonm10 - Sorry, I messed that up.
Column = VAR __Current = [Value] VAR __PreviousDate = MAXX(FILTER('Test','Test'[Date] < EARLIER('Test'[Date]) && [Group] = EARLIER([Group])),[Date]) VAR __Previous = MAXX(FILTER('Test','Test'[Date] = __PreviousDate && [Group] = EARLIER([Group])),[Value]) RETURN __Current - __Previous
- amitchandak
Super User
sonm10 , Try a new column like
New column = Var _Last_date = maxx(filter(table,[Date]<earlier([Date])),[Date]) var _Last_value = maxx(filter(table,[Date]=_Last_date),[Value]) return divide([Value]-_Last_value,_Last_value)- sonm10
Thank you amitchandak for your solution. However, since I have multiple items on the same date and i want the calculation to be done for each separate item, its not working. Your solution is working if there is no multiple item
- mahoneypat
Microsoft Employee
Please try this column expression that gets your desired results.
Change =
VAR thisvalue = 'Table'[Value]
VAR thisitem = 'Table'[Item]
VAR thisdate = 'Table'[Date]
VAR prev =
CALCULATE (
LASTNONBLANKVALUE ( 'Table'[Date], MAX ( 'Table'[Value] ) ),
ALL ( 'Table' ),
'Table'[Date] < thisdate,
'Table'[Item] = thisitem
)
RETURN
DIVIDE ( thisvalue - prev, prev )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
