Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Solved! Go to Solution.
Cool question! I created a mockup of your data set (it's better to copy it into your question next time, rather than a screenshot, so we can replicate faster). I've loaded this into Power BI. The first thing I did was filter out irrelevant information (in the Query Editor), such that the Function column only shows ON or OFF. I then added an Index column, so my table looks like this when I start making my report:
Now, we need to lookup the next Time value with a change of Function. For clearity, we add it as a calculated column now. The formula is:
NextStatusChangeTime = CALCULATE(MIN(Table1[Time]), FILTER(Table1, Table1[Function] <> EARLIER(Table1[Function]) && Table1[Time] > EARLIER(Table1[Time])))
This looks at the table, applies a filter on it (by looking for a status other then current row Status, and to look for Time later then the current row Time. This result in the following table:
Next up is calculating the TimeDiff. We do this with the following calculated column:
TimeDiff = DATEDIFF(Table1[Time], Table1[NextStatusChangeTime], SECOND)
Ofcourse you can change the time unit to whatever you want it to be, for example MINUTE. This results in the following table:
With this, you can calculate the total amount of active time, by creating a measure that sums the column TimeDiff where Function is ON:
TotalActiveTime = CALCULATE(SUM(Table1[TimeDiff]), FILTER(ALL(Table1), Table1[Function] = "ON"))
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
Proud to be a Super User!
Cool question! I created a mockup of your data set (it's better to copy it into your question next time, rather than a screenshot, so we can replicate faster). I've loaded this into Power BI. The first thing I did was filter out irrelevant information (in the Query Editor), such that the Function column only shows ON or OFF. I then added an Index column, so my table looks like this when I start making my report:
Now, we need to lookup the next Time value with a change of Function. For clearity, we add it as a calculated column now. The formula is:
NextStatusChangeTime = CALCULATE(MIN(Table1[Time]), FILTER(Table1, Table1[Function] <> EARLIER(Table1[Function]) && Table1[Time] > EARLIER(Table1[Time])))
This looks at the table, applies a filter on it (by looking for a status other then current row Status, and to look for Time later then the current row Time. This result in the following table:
Next up is calculating the TimeDiff. We do this with the following calculated column:
TimeDiff = DATEDIFF(Table1[Time], Table1[NextStatusChangeTime], SECOND)
Ofcourse you can change the time unit to whatever you want it to be, for example MINUTE. This results in the following table:
With this, you can calculate the total amount of active time, by creating a measure that sums the column TimeDiff where Function is ON:
TotalActiveTime = CALCULATE(SUM(Table1[TimeDiff]), FILTER(ALL(Table1), Table1[Function] = "ON"))
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
Proud to be a Super User!
Thank you for the fabulous response @JarroVGIT .
Makes perfect sense.
Appreciate you taking the time and effort to explain
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |