Calculate duration of states

[Anonymous]

Hello All,

I am trying to calculate the duration between two on and off states.

Sample data :- 

Date	Time	Status
7/7/2018	9:59:53 AM	ON
7/7/2018	10:50:53 AM	OFF
7/8/2018	9:26:52 AM	ON
7/8/2018	10:20:52 AM	OFF
7/9/2018	3:59:51 AM	ON
7/9/2018	4:14:51 AM	OFF
7/9/2018	5:50:50 AM	ON
7/9/2018	7:35:50 AM	OFF
7/9/2018	8:26:50 AM	ON
7/9/2018	8:35:50 AM	OFF

Now i would lik to calcualte the duration from states between ON to OFF but not OFF to ON.

i.e nothing but, i am trying to calculate only the total ON duration.

For Ex:- 

In above data for 7/9/2018

7/9/2018	3:59:51 AM	ON	0
7/9/2018	4:14:51 AM	OFF	0.15
7/9/2018	5:50:50 AM	ON	0
7/9/2018	7:35:50 AM	OFF	1.35
7/9/2018	8:26:50 AM	ON	0
7/9/2018	8:35:50 AM	OFF	0.9

How can i get this done in dax.

Please suggest me.

Thanks,

Mohan V

[Iryna_K]

Hi, 

The solution I propose is not exactly for Power-BI, but there is an extension on the marketplace that will help you with your request https://marketplace.visualstudio.com/items?itemName=SaaSJet.tis-azure .

With Time in State, you can quickly get reports on how long each Work Item has been in a particular state, how many times states have changed, etc.

Charts are also available for display. I hope you find a helpful solution.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please firstly add an index column in Query Editor mode. Then, create a calculated column with below DAX formula:

Duration =
IF (
    TB1[Status] = "OFF",
    DATEDIFF (
        CALCULATE (
            MAX ( TB1[Time] ),
            FILTER (
                ALLEXCEPT ( TB1, TB1[Date] ),
                TB1[Index]
                    = EARLIER ( TB1[Index] ) - 1
                    && TB1[Status] = "ON"
            )
        ),
        TB1[Time],
        MINUTE
    ),
    0
)

Regards,

Yuliana Gu

[andreabrezmes]

Hello!

I think my solution could be similar to this one, but I need to incorporate the date in axis.. Could you please take a look? Real thanks! 

https://community.powerbi.com/t5/Desktop/Distinct-Count-of-Last-Status-over-time/m-p/647252 

(You can copy paste link, site is working weird today)

[Seward12533]

Dont have time right now to think this through but to get you ponted in the right direction, you need to use a calcualted column with EARLIER in the filter crtieria.