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
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.
Hi Anonymous,
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.
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
Thank you for your help. I'll try this solution. But doing this will only work with a matrix visualiztion, right?
Hi Anonymous,
Thank you for your help and time. It's working accurately now.