Forum Discussion

elsteshepardo's avatar
elsteshepardo
Frequent Visitor
3 years ago

Calculate difference between two status changes

Hi,

I foolishly 'broke' a powerbi report by accidentally deleting a query and additional columns I'd added by using Google & this forum

I now can't find the right answer

 

I have a sharepoint list which is a transaction log - keeping a track on status changes for new requests.

The table looks a bit like this

Title (Request number)StatusModified (date)
001Received20/3/23
001Reviewed25/3/23
002Received30/3/23
003Received1/4/23
002Reviewed10/4/23
004Received15/4/23

 

As you can see, it's just a series of status changes and the date is was made - it's from a Flow that runs each time an item is updated. Anyway, what I need is a duration (difference) between status updates per Request

So in the above table it would have something like

TitleStatusDuration
001Received=25/3/23 - 20/3/23 = 5 days
002Received=10/4/23 - 30/3/23 = 11 days

 

Could someone help - I seem to remember it was a calculated column

Thanks

Steven

Need Help

5 Replies

  • DataVitalizer's avatar
    DataVitalizer
    Super User
    3 years ago


    Hi elsteshepardo 

    Can you open the advanced editor of your table? If so paste here to make the edit easy for you. 


    Did it work ? 👌 Mark it as a solution to help spreading knowledge 👉 A kudos would be appreciated

    • elsteshepardo's avatar
      elsteshepardo
      Frequent Visitor
      3 years ago

      Hi, not sure what you mean by that?

      The table in the dataset? Or the query?

    • elsteshepardo's avatar
      elsteshepardo
      Frequent Visitor
      3 years ago

      Is this what you mean? I opened advanced editor from the query:

       

      let
      Source = SharePoint.Tables("https://nhs.sharepoint.com/sites/*********", [Implementation="2.0", ViewMode="All"]),
      #"b0118a61-d46c-4d5f-ade5-8b12eaec27f8" = Source{[Id="b0118a61-d46c-4d5f-ade5-8b12eaec27f8"]}[Items],
      #"Removed Other Columns" = Table.SelectColumns(#"b0118a61-d46c-4d5f-ade5-8b12eaec27f8",{"Title", "Status", "Created", "Modified", "ID", "Version"})
      in
      #"Removed Other Columns"

      • elsteshepardo's avatar
        elsteshepardo
        Frequent Visitor
        3 years ago

        TitleStatusCreatedModifiedIDVersionStatus Order

        401For assessment25/01/2023 12:38:0025/01/2023 12:38:0031.03
        301For assessment25/01/2023 13:16:0025/01/2023 13:16:0041.03
        401Under investigation25/01/2023 13:16:0025/01/2023 13:16:0051.02
        401For SLT review25/01/2023 14:53:0025/01/2023 14:53:0061.04
        243Approved to next stage26/01/2023 10:35:0026/01/2023 10:35:00141.07
        244Approved to next stage26/01/2023 10:35:0026/01/2023 10:35:00151.07
        248Approved to next stage26/01/2023 10:40:0026/01/2023 10:40:00161.07
        242Approved to next stage26/01/2023 11:11:0026/01/2023 11:11:00171.07
        301Request completed23/02/2023 14:39:0023/02/2023 14:39:00181.010
        301Request completed23/02/2023 14:39:0023/02/2023 14:39:00191.010
        301Request received23/02/2023 14:44:0023/02/2023 14:44:00201.01
        301For SLT review23/02/2023 14:46:0023/02/2023 14:46:00211.04
        301Approved to next stage23/02/2023 14:48:0023/02/2023 14:48:00221.07
        301Request now a project23/02/2023 14:49:0023/02/2023 14:49:00231.09
        262Request received23/02/2023 15:23:0023/02/2023 15:23:00241.01
        262Under investigation24/02/2023 08:44:0024/02/2023 08:44:00251.02
        259Under investigation24/02/2023 08:44:0024/02/2023 08:44:00261.02
        261Under investigation24/02/2023 08:44:0024/02/2023 08:44:00271.02
        262For assessment01/03/2023 11:20:0001/03/2023 11:20:00281.03
        240Request completed02/03/2023 15:20:0002/03/2023 15:20:00291.010
        228Request completed02/03/2023 15:29:0002/03/2023 15:29:00301.010
        232Approved to next stage08/03/2023 12:55:0008/03/2023 12:55:00321.07
        233Approved to next stage08/03/2023 12:57:0008/03/2023 12:57:00331.07
        236Approved to next stage08/03/2023 12:58:0008/03/2023 12:58:00341.07
        252Approved to next stage08/03/2023 13:00:0008/03/2023 13:00:00351.07
        253Approved to next stage08/03/2023 13:01:0008/03/2023 13:01:00361.07
        254Approved to next stage08/03/2023 13:03:0008/03/2023 13:03:00371.07
        255Approved to next stage08/03/2023 13:03:0008/03/2023 13:03:00381.07
        256Approved to next stage08/03/2023 13:04:0008/03/2023 13:04:00391.07
        257Approved to next stage08/03/2023 13:05:0008/03/2023 13:05:00401.07
        258Approved to next stage08/03/2023 13:06:0008/03/2023 13:06:00411.07
        265Under investigation09/03/2023 14:43:0009/03/2023 14:43:00421.02
        264Under investigation09/03/2023 14:43:0009/03/2023 14:43:00431.02
        229For SLT review09/03/2023 15:15:0009/03/2023 15:15:00441.04
        266Under investigation14/03/2023 09:45:0014/03/2023 09:45:00451.02
        263For SLT review14/03/2023 11:03:0014/03/2023 11:03:00461.04
        229Approved to next stage14/03/2023 11:18:0014/03/2023 11:18:00471.07
        266For assessment15/03/2023 10:38:0015/03/2023 10:38:00481.03
        176Request completed17/03/2023 13:55:0017/03/2023 13:55:00491.010
        213Request completed17/03/2023 16:25:0017/03/2023 16:25:00501.010
        268For Clinical review23/03/2023 14:31:0023/03/2023 14:31:00511.05
        208Request completed23/03/2023 16:13:0023/03/2023 16:13:00521.010
        217Request completed23/03/2023 16:14:0023/03/2023 16:14:00531.010
        243Request completed23/03/2023 16:19:0023/03/2023 16:19:00541.010
        259For assessment24/03/2023 14:42:0024/03/2023 14:42:00551.03
        265For SLT review28/03/2023 09:28:0028/03/2023 09:28:00561.04
        267Approved to next stage28/03/2023 12:05:0028/03/2023 12:05:00571.07
        263Approved to next stage28/03/2023 12:05:0028/03/2023 12:05:00581.07
        264Approved to next stage28/03/2023 12:06:0028/03/2023 12:06:00591.07
        260Approved to next stage28/03/2023 12:14:0028/03/2023 12:14:00601.07
        271For assessment30/03/2023 09:42:0030/03/2023 09:42:00611.03
        205Request completed31/03/2023 10:11:0031/03/2023 10:11:00621.010
        227Request completed31/03/2023 14:27:0031/03/2023 14:27:00631.010
        272Under investigation03/04/2023 10:24:0003/04/2023 10:24:00641.02
        270For assessment03/04/2023 16:13:0003/04/2023 16:13:00651.03
        271For SLT review04/04/2023 08:54:0004/04/2023 08:54:00661.04
        270For SLT review04/04/2023 08:54:0004/04/2023 08:54:00671.04
        259For SLT review04/04/2023 08:54:0004/04/2023 08:54:00681.04
        268For assessment04/04/2023 15:35:0004/04/2023 15:35:00691.03
        259Approved to next stage11/04/2023 10:17:0011/04/2023 10:17:00701.07
        270Rejected11/04/2023 10:18:0011/04/2023 10:18:00711.06
        271Approved to next stage11/04/2023 10:18:0011/04/2023 10:18:00721.07
        265Approved to next stage11/04/2023 10:18:0011/04/2023 10:18:00731.07
        165Request completed11/04/2023 14:22:0011/04/2023 14:22:00741.010
        211Request completed11/04/2023 14:33:0011/04/2023 14:33:00751.010
        174Approved to next stage11/04/2023 15:55:0011/04/2023 15:55:00761.07
        275Under investigation12/04/2023 09:28:0012/04/2023 09:28:00771.02
        246Rejected12/04/2023 09:42:0012/04/2023 09:42:00781.06
        271Request now a project12/04/2023 11:20:0012/04/2023 11:20:00791.09
        235Rejected12/04/2023 16:06:0012/04/2023 16:06:00801.06
        275For Clinical review17/04/2023 11:35:0017/04/2023 11:35:00811.05
        278For Clinical review17/04/2023 11:35:0017/04/2023 11:35:00821.05
        273For Clinical review17/04/2023 11:35:0017/04/2023 11:35:00831.05
        276Proceeded without SLT approval17/04/2023 13:36:0017/04/2023 13:36:00841.08
        274For assessment17/04/2023 13:40:0017/04/2023 13:40:00851.03
        274For SLT review18/04/2023 10:33:0018/04/2023 10:33:00861.04
        277For SLT review18/04/2023 10:33:0018/04/2023 10:33:00871.04
        209Request completed18/04/2023 12:40:0018/04/2023 12:40:00881.010
        234Rejected18/04/2023 14:05:0018/04/2023 14:05:00891.06
        171Rejected20/04/2023 09:39:0020/04/2023 09:39:00901.06
        183Rejected20/04/2023 09:49:0020/04/2023 09:49:00911.06
        231Rejected20/04/2023 09:51:0020/04/2023 09:51:00921.06
        275For assessment20/04/2023 16:14:0020/04/2023 16:14:00931.03
        278For assessment20/04/2023 16:14:0020/04/2023 16:14:00941.03
        273For assessment20/04/2023 16:14:0020/04/2023 16:14:00951.03
        178For assessment20/04/2023 16:15:0020/04/2023 16:15:00961.03
        232For SLT review21/04/2023 12:36:0021/04/2023 12:36:00971.04
        274Approved to next stage25/04/2023 09:45:0025/04/2023 09:45:00981.07
        277Approved to next stage25/04/2023 09:45:0025/04/2023 09:45:00991.07