Forum Discussion
Determining Previous Status in Table with Same ID
Hi Alex215 ,
The value of the date column is not unique, please first create a new index column.
Please create a new calculated column.
Past Status = VAR _index = CALCULATE ( MIN ( 'Table'[Index] ), FILTER ( 'Table', 'Table'[Index] >= EARLIER ( 'Table'[Index] ) && 'Table'[Field] = "status" && 'Table'[id] = EARLIER('Table'[id]) ) ) VAR _status = CALCULATE ( MAX ( 'Table'[OldValue] ), FILTER ( 'Table', 'Table'[Index] = _index ) ) RETURN _statusAttach the PBIX file for reference. Hope it helps.
Best Regards,
Community Support Team_GaoIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Hi Alex215 ，
May I ask a question? In the data you posted, are the two lines returned wrong? I'm stuck here.
According to the above rules, should the result they return be Random Text?
Best Regards,
Community Support Team_Gao
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Hi Anonymous
No sadly. That is why I am also getting stuck at the same point.
The raw data I am getting in are rows of history changes made to a lot of different forms (identified by "id"). I have them sorted by "id" and "CreatedDate" so they appear in the order as shown above. Every change made adds an additional row. The goal of the "Past Status" column is to determine what the Status was when the change was made no matter the type of change which is the "Field" column.
The goal is to calculate what the average duration of time between edits to the same form (identified by "id") while the "Past Status" is equal to New or Active.
| id | Field | OldValue | NewValue | CreatedDate | Past Status | Duration |
| 1 | created | null | null | 1/1/2022 | New | null |
| 1 | name | Test1 | Test2 | 1/5/2022 | New | 4 |
| 1 | status | New | Active | 1/6/2022 | New | 1 |
| 1 | Description | Random Text | Random Text | 1/7/2022 | Active | 1 |
| 1 | status | Active | Archived | 1/9/2022 | Active | 2 |
| 1 | Description | Random Text | Random Text | 1/12/2022 | Archived | 3 |
| 1 | status | Archived | Active | 1/23/2022 | Archived | 11 |
| 1 | status | Active | Archived | 1/23/2022 | Active | 0 |
In this case the average duration for "id"=1 while past status = "New" or "Active" would be 1.6 days ((4+1+1+2+0)/5). I just need help figuring out how to make the "Past Status" column.