Forum Discussion
Extract duration from date/time in different rows at different instances
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
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
11 Replies
Is there always a null value between load shedding periods (as shown in the final image you shared)?
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.
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
