I need to know count of days between Status changing

[dimuch]

Hi All,
I need to know count of days between Status changing.

For example
Asset with InvOrg 20451S was
377 days on Storage, then 4 days in Transit, then 17 days in Production, then 13 days on Storage, then 387 days in Production to now.
So I need to know how to calculate it.
Thanks.

[v-huizhn-msft]

Hi @dimuch,

I try to reproduce your scenario and get expected result. I create the following sample table.



Right click the table->Edit Quey, please add an index column to identify each row.



Create a calculated column to get the previous row status.

Previous status = IF(Test1[Index]=1,Test1[Status],LOOKUPVALUE(Test1[Status],Test1[Index],Test1[Index]-1))

Finally, create a new table to get the expected result. Please click the "New Table" under modeling on Home page.



Thanks,
Angelia

[dimuch]

HI,
I used your formula. But, it display incorrect Previous status

[v-huizhn-msft]

Hi @dimuch,

Your resource table is same with the table given above? And do you add a index column, why there is blank in your result. I guess the table you given is not the resouce table. Please check carefully, I create the sample table based on your table, but it's not same with your table. 

Best Regards,
Angelia

[Greg_Deckler]

What does your source data look like? Can you post it in a table (not an image) so that we can mock it up?