Determining Previous Status in Table with Same ID

[Alex215]

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

[Anonymous · Accepted solution]

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
    _status

Attach the PBIX file for reference. Hope it helps.

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

[Alex215]

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!

[Anonymous]

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

[Alex215]

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.