Forum Discussion
Helper IVTrack/Create Revision History
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
2 Replies
- amitchandak
Super User
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-data-to-it-using-incremental-refresh/
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)
bob57Thank you! I shall move forward with the reading and study your solution. I now have a direction.
