Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

DateDiff between rows of Status changes

Hello all,

 

I am a new user to PowerQuery and PowerPivot, and have been very pleased with the potential it has for automating some of the reports I complete weekly at the expense of hours.  

 

My reporting is based on a IT Ticketing system called FootPrints ServiceCore 11.6, I have read-only access to.

 

There are only a few components I have struggled to figure out, this being one of the most challenging for me.

 

One of the core tables of data is the FIELDHISTORY table, which tracks status changes of tickets.  An example of this would be OPEN -> Work-In-Progress, then from Work-In-Progress to Closed.  Each status change represents a new row in the table, each with it's own timestamp.  (See below IMG for reference).  I need to calculate the DateDiff of timestamps for each ticket (down to the minute) in sequence between each status change.  These numbers will then be summed together for the total ticket age metric, with the option for management to remove time in certain statuses at their discretion.

 

Moreover, the last timestamp should always show a datediff between the timestamp and NOW(), for obvious reasons.

 

 

Any help in accomplishing this task would be greatly appreciated.

 

Sincerely,

Kristopher

Need Help

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Greg_Deckler ,

       

      Thank you for your prompt reply to my inquiry.  I've read through your article and think it is a brilliant algorithm for calculating concurrent timestamps for calculating MTBF.  I tried adjusting the code to fit my scenario, but unfortunately come across an ERROR when I attempt to create the new column with the VAR Next code.  Apologies for my naivete with regard to PwrQuery and DAX.

       

      I get these errors any time I try to use code with a variable declaration in the beginning, is there something I am doing wrong or misunderstanding with regard to DAX functionality.

       

      StsTimeDiff = 

VAR next = MINX(FILTER(MASTER3_FIELDHISTORY,

                       MASTER3_FIELDHISTORY[mrID]=EARLIER(MASTER3_FIELDHISTORY[mrID]) &&

                       MASTER3_FIELDHISTORY[mrTIMESTAMP]>EARLIER(MASTER3_FIELDHISTORY[mrTIMESTAMP])

               ),MASTER3_FIELDHISTORY[mrTIMESTAMP])

RETURN IF(ISBLANK(next),

               DATEDIFF([mrTIMESTAMP],NOW(),MINUTE),

               DATEDIFF([mrTIMESTAMP],next,MINUTE)

           )

      Error: Token Eof expected.

      Show error highlights the VAR next segment at the top.

       

      Steps I took, for reference:

      1. Go to Power Query tab
      2. Entered Power Query table/query editor
      3. Select Add Column tab
      4. Select Custom Column button
      5. Name the column, "StsTimeDiff"
      6. Paste code starting at VAR
      7. Receive the Token Eof Expected error, can't click okay.

       

       

      Sincerely,

      Kristopher

       

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Apologies for my lack of understanding, Greg_Deckler ,

       

      Someone from another post clarified the difference between M-Code and DAX.  I will need to verify this works in Power Pivot with DAX, or otherwise see if a solution in M-Code is obtainable.

       

      I will reach back out to confirm once I have secured a solution to confirm.  

       

      Sincerely,

      Kristopher