Forum Discussion
Calculate the time an object is active within time interval selected
Hi
I'm looking to calculate the time that a given object is active within a chosen timespan on the date dimension. I've tried to illustrate this in the picture here:
picture example: in this example the measure should return, that the object has been active for 15 days.
data:
| object id | status | start time | end time |
| 1 | active | 25/03/2023 | 05/04/2023 |
| 1 | down | 05/04/2023 | 15/04/2023 |
| 1 | active | 15/04/2023 | 25/04/2023 |
| 1 | down | 25/04/2023 | 05/05/2023 |
Result wanted:
| object id | active days |
| 1 | 15 |
data model:
model explanation: the calendar dimension is connected to the fact with 2 inactive connecters. one to the start date and one to the end date.
First i tried just calculating the time difference on between the dates in a new column on the fact, and wrote this calculation:
but this will not give the correct output. since it returns 0 if the start or end dates are not within the calendar selection. Also it will return the full amount of time (10 days) for the first active event in the picture above and not 5 days as needed.
Does anyone have an idea of how to handle this request?
5 Replies
- lbendlin
Super User
Your End Time and Start Time values overlap. That will not work / lead to duplication. You need better granularity.
thanks for reply 🙂
I'm not sure i see the same issue. Because i filter on status = active, so they will never overlap. Is there something in the background of PBI that I'm not aware of?
also what i'm trying to achive is basically an "event in progress calculation" but instead of counting events i want to sum the time.- lbendlin
On 5/4/2023 was your Object 1 active or was it down?
