Forum Discussion
Extract duration from date/time in different rows at different instances
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
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
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
