Extract duration from date/time in different rows at different instances

[s_jay]

Hello! I am new to this community so I apologize if there is any error in this post.
I need to find difference between a number of rows and repeat it and then add all these difference values up. The whole problem is as follows:

I have input voltage and currrent data of three days from three different locations. Based on this data I have to analyze the load shedding pattern at all of these locations including total load shedding hours per day at all sites, load shedding hours per day per site and total load shedding hours in all three days. I have a single table that has a location column, date/time column followed by voltages and currents columns. The condition for loadshedding is when all three input votage values go to zero, load shedding begins. Based on this condition I have created a new colum in query editor called loadshedding which stores 1 when input voltages are zero (i.e. load shedding in progress) and zero otherwise. Next I have created a Time_stamp column to save date/time values for every instance when load shedding column is 1. Now I need to subtract the start and end values to get duration. One approach that I used was to use index column, lookup and datediff in data view to get the difference of every row from its next row in Time_stamp column and I decided to sum these values at visual level in report. However, this isn't working. I always get an error with datediff that says "start date can't be greater than end date". I think this is because I am unable to find a way to mark start and end of a day.
I believe there must be other completely different, easier and better approaches to address and solve this problem. I read about merging and joining column to itself but didn't get that.

Also, I'd like to work with query editor (M language) at data import time instead of working with DAX at data view, as I have done already, So please guide me in this regard. Please see the image files attached.

Thank you.

General form of TableTableMultiple occurrences like this are to be handled

[dkay84_PowerBI]

Is there always a null value between load shedding periods (as shown in the final image you shared)?

[s_jay]

Thank you for your time. Yes there is always a null value between load shedding periods. This is because I have created Time_stamps column as a conditional column as sown in the image below:

Time_stamps column

I am trying to attach my .pbix file but I cannot find an "attach file" option in the post.

[v-shex-msft]

Hi @s_jay,

You can upload to your network drive(e.g onedrive google drive, dropbox), share the file and paste the share link here.

Regards,

Xiaoxin sheng

[s_jay]

Thank you for your reply and sorry for responding late. I have made some progress. I have created a new column called load_sheddinghours that stores the difference of every value in Time_stamps column from the value next to it in the column to get duration of loadsheding. I can now get the total hours by summing the values in this column but that is done at visual level. I also want to view the minimum and average load shedding hours per site per day and during all three days. But I am getting the minimum and average of the entire column at visual level (can be seen in the report view).

Following is the link for my .pbix file. Thank you for your help.

https://drive.google.com/file/d/0B6F8ywN6G9GkSkpVWnR1RlhXWm8/view?usp=sharing

[v-shex-msft]

Hi @s_jay,

You can refer to bleow steps if it suitable for your requirement:

1. Add calculated column Tag to mark the begin and end of the date range.

Tag = 
var prevIndex=LOOKUPVALUE('Data_3 sites'[Load_Shedding],[Index],MAXX(FILTER(ALL('Data_3 sites'),[Sites]=EARLIER([Sites])&&[Index]<EARLIER('Data_3 sites'[Index])),[Index]))
var nextIndex=LOOKUPVALUE('Data_3 sites'[Load_Shedding],[Index],MINX(FILTER(ALL('Data_3 sites'),[Sites]=EARLIER([Sites])&&[Index]>EARLIER('Data_3 sites'[Index])),[Index]))
var currIndex=LOOKUPVALUE([Load_Shedding],'Data_3 sites'[Index],[Index],[Sites],[Sites])
return
if(prevIndex<>currIndex||[Index]=MINX(ALL('Data_3 sites'),[Index]),"Begin",if(nextIndex<>currIndex||[Index]=MAXX(ALL('Data_3 sites'),[Index]),"End",BLANK()))

2. Filter Original Table with non blank Tag.

Table = CALCULATETABLE('Data_3 sites','Data_3 sites'[Tag]<>BLANK())

3. Add calculated column to calculate the duration.

Duration = SWITCH([Tag],
    "Begin",DATEDIFF([Date Time],MINX(FILTER(ALL('Table'),[Sites]=EARLIER([Sites])&&[Load_Shedding]=EARLIER([Load_Shedding])&&[Date Time]>EARLIER([Date Time])),[Date Time]),SECOND),
    "End",DATEDIFF(MAXX(FILTER(ALL('Table'),[Sites]=EARLIER([Sites])&&[Load_Shedding]=EARLIER([Load_Shedding])&&[Date Time]<EARLIER([Date Time])),[Date Time]),[Date Time],SECOND),
    BLANK())

4. Create matrix visual to show the result.

Regards,

Xiaoxin Sheng

[s_jay]

Hi! Thank you so much for your help. I'll try it the way you have described and let you know if further help is needed. 

[s_jay]

Hi @v-shex-msft,

I tried according to your suggestion. The Duration column is currently recording values against every row. I need it to store the difference between corresponding begin and end periods and that too only when Load_shedding column is 1 because I just need to record the load shedding period values. So that when I sum this column I get the total load shedding duration per location per day. (Not restricted to matrix visual only)

Also, I was thinking of using pivot column to get sum, average, minimum and maximum of duration values per location or per day (as required) in query editor but I don't know how to do this in data view with DAX as I am very new to this. Please assist in this regard as well.

Thank you for your time and help.

[v-shex-msft · Accepted solution]

Hi @s_jay,

>>I need it to store the difference between corresponding begin and end periods and that too only when Load_shedding column is 1 because I just need to record the load shedding period values.

You can modify the table formula and add a condition to filter specific records:

Table = CALCULATETABLE('Data_3 sites','Data_3 sites'[Tag]<>BLANK(),'Data_3 sites'[Load_Shedding]=1)

>>Also, I was thinking of using pivot column to get sum, average, minimum and maximum of duration values per location or per day (as required) in query editor but I don't know how to do this in data view with DAX as I am very new to this. 

The most simple way is turn on the total row, and drag other duration columns with different summary mode.

Regards,

Xiaoxin Sheng

[s_jay]

Thank you for your help. I'll try this solution. But doing this will only work with a matrix visualiztion, right? 

[v-shex-msft]

Hi @s_jay,

I choose the matrix to use total row feature to summary result.

Regards,
Xiaoxin Sheng

[s_jay]

Hi @v-shex-msft,

Thank you for your help and time. It's working accurately now.