cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bob57
Helper IV
Helper IV

Track/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".

bob57_0-1643745488575.png

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

bob57_1-1643745985470.png

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

bob57_2-1643746517739.png

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 2
amitchandak
Super User
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-...
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)

 



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors