Forum Discussion

s_jay's avatar
s_jay
Frequent Visitor
9 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
s_jay
Frequent Visitor
9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      s_jay
      Frequent Visitor
      9 years ago

      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's avatar
        s_jay
        Frequent Visitor
        9 years ago

        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.