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 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