Forum Discussion
Determining Previous Status in Table with Same ID
Hi,
Need some help to determine the best way to approach the below problem. The table below is a good example of what my raw data looks like. I am trying to create the "Past Status" column below based per "id". If there is no "Past Status" it would just be equal to New like my example below.
| id | Field | OldValue | NewValue | CreatedDate | Past Status |
| 1 | created | null | null | 1/1/2022 | New |
| 1 | name | Test1 | Test2 | 1/5/2022 | New |
| 1 | status | New | Active | 1/6/2022 | New |
| 1 | Description | Random Text | Random Text | 1/7/2022 | Active |
| 1 | status | Active | Archived | 1/9/2022 | Active |
| 1 | Description | Random Text | Random Text | 1/12/2022 | Archived |
| 1 | status | Archived | Active | 1/23/2022 | Archived |
| 1 | status | Active | Archived | 1/23/2022 | Active |
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
4 Replies
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_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 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.
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 Anonymous,
Thank you for that information. That worked! I do not use DAX very much so I never even thought to approach the problem that way.
I appreciate all your help!
