Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
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
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:
Sincerely,
Kristopher
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.