Track/Create Revision History

[bob57]

Hello PBI Community,

A client is requesting that I calculate the number of days a record remains in a particular status. In this (over simplified) example below, I need to calculate the number of days that a file remained "on hold". The first field is the File Id, the second field is the date that the record was created, and the third field defines the record's status.

 The status for all records in the table below is "in process".

In the table beow, the status for record 103 changed to "on hold" after refresh on 1/22/2022.

In the table below, the status for record 103 returns to "in process" after refressh on 1/27/2022, so record 103 was on hold for 5 days

Is there a way to calculate this number (5 days on hold) with the limited data I am presented? There is no date field to indicate when the status changed.

I hope this write-up was clear and thank you for your time.

Bob

[amitchandak]

@bob57 , first of all, you need a snapshot of the data for that. Do you have that in Power BI? With refresh date as one of the column

If you do not have a snapshot, you need to check DAX append

https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

Post that for same ID you can check for status on old snapshot

new column =

var _max = maxx(filter(Table, [Fieled] = earlier([Fieled] ) && [Create date] < earlier([Create date]) && [Status] <> earlier([Status]) ) , [Snapshot date])

return

datediff(maxx(filter(Table, [Fieled] = earlier([Fieled] ) && [ [Snapshot date]] = _max ) , [Create date]) , [Create date], day)

[bob57]

Thank you! I shall move forward with the reading and study your solution. I now have a direction.