Issues in Power BI Calculating DATEDIFF Between Two Records

0
votes

I have been working on a Power BI report that's pulling data from Jira's database and displaying various information about the Jira issues. I'm struggling with one measure for "Time in Status" that is attempting to show the DATEDIFF between each status. The data looks like this:

| Issue_ID  |  Old_Status  |  New_Status  |  Status_Change_Date   |  Issue_Created_Date  |  Sequence  |
=======================================================================================================
| JIRA-001  |    To Do     |  In Progress |  06/05/2020 08:00 AM  | 06/01/2020 04:00 PM  |     1      |
-------------------------------------------------------------------------------------------------------
| JIRA-001  |  In Progress |   Testing    |  06/07/2020 02:00 PM  | 06/01/2020 04:00 PM  |     2      |
-------------------------------------------------------------------------------------------------------
| JIRA-001  |   Testing    |     Done     |  06/10/2020 09:45 AM  | 06/01/2020 04:00 PM  |     3      |
-------------------------------------------------------------------------------------------------------
| JIRA-002  |    To Do     |  In Progress |  06/03/2020 09:00 AM  | 06/02/2020 11:30 AM  |     1      |
-------------------------------------------------------------------------------------------------------
| JIRA-002  |  In Progress |   Testing    |  06/03/2020 03:45 PM  | 06/02/2020 11:30 AM  |     2      |

I've been attempting to create a Measure that looks at the Sequence number and then calculates the date/time difference between the Status_Change_Date of the specific row and the Status_Change_Date from previous record in the sequence. If the Sequence number is "1" then it should calculate the date/time difference between the Issue_Created_Date and the Status_Change_Date.

Here are the (somewhat oversimplified) results that I'm trying to get:

| Issue_ID |    Status    |        Time_in_Status         |
===========================================================
| JIRA-001 |    To Do     |        3 Days, 8 Hours        |
-----------------------------------------------------------
| JIRA-001 |  In Progress |        2 Days, 6 Hours        |
-----------------------------------------------------------
| JIRA-001 |    Testing   |    2 Days, 19 Hours, 15 Mins  |
-----------------------------------------------------------
| JIRA-002 |    To Do     |        21 Hours , 30 Mins     |
-----------------------------------------------------------
| JIRA-002 |  In Progress |        6 Hours 45 Mins        |

I would be fine with the Time_In_Status showing a number with a decimal place (2.25 Days for instance). The Measures I've attempted to create, however, don't see to work.

Time_in_Status = IF(x.[Sequence] = 1, 
                    DATEDIFF(x.[Issue_Created_Date], x.[Status_Change_Date], DAYS),
                    [I don't know how to do determine the DATEDIFF from the previous row by [Sequence]])

I know the Measure is sloppy but I'm still pretty green with DAX and Power BI.

Anyone have any ideas of how I achieve the results I want with that data?

UPDATE: There is a second table that is not being used in the DATEDIFF measure, but is being used to pull other data into the report and it's being used to filter the report using Sliders. The two tables are connected by the Issue_ID and the Issue_ID is not distinct in either of the tables. Here is the second table:

| Issue_ID  |  Issue_Title  |  Product_Team  |  Epic_ID   |  Epic_Version  |     Sprint     |   Component   |
=============================================================================================================
| JIRA-001  |  Do a thing   |      Mobile    |  JIRA-101  |     1.1.31     |   Sprint 1.1   |      iOS      |
-------------------------------------------------------------------------------------------------------------
| JIRA-001  |  Do a thing   |      Mobile    |  JIRA-101  |     1.1.31     |   Sprint 1.2   |      iOS      |
-------------------------------------------------------------------------------------------------------------
| JIRA-002  |  Do another   |      Mobile    |  JIRA-101  |     1.1.33     |   Sprint 1.1   |      iOS      |
-------------------------------------------------------------------------------------------------------------
| JIRA-003  |  Yet another  |       Web      |  JIRA-102  |     1.1.40     |   Sprint 1.1   |     Python    |
-------------------------------------------------------------------------------------------------------------

The Sliders/filters I'm using are all from the second table (y). Those filters are:

  • Product_Team
  • Epic_Version
  • Sprint
  • Component
1
sqlpowerbidax

1 Answers

0
votes

Another user provided the answer but then all the comments were deleted somehow. The answer they provided got me 95% of the way there, but there was an error it took me a few weeks to get around.

The actual answer involved creating two measures: "Minutes in Status" and "Days in Current Status."

Minutes in Status:

Minutes in Status = 
SUMX(
    'x',
    VAR Current_Sequence = 'x'[Sequence]
    VAR Current_Change_Date = 'x'[Change_Date]
    VAR Current_Created_Date = 'x'[Created_Date]
    VAR Issue_ID = 'x'[Issue_ID]
    VAR Previous_Change_Date = 
            CALCULATE(
                MAX('x'[Change_Date]),
                ALLEXCEPT('x', 'x[Issue_ID]),
                'x'[Sequence] = Current_Sequence - 1
            )
    VAR Previous_Date = 
            IF(
                Current_Sequence = 1, Current_Created_Date, Previous_Change_Date)
RETURN
    CALCULATE(
        DATEDIFF(
            Previous_Date, Current_Change_Date, MINUTE))
)

Days in Current Status:

Days in Current Status = 
    VAR Total_Minutes = [Minutes in Status]
    VAR Total_Hours = INT(Total_Minutes/60)
    VAR Remaining_Minutes = MOD(Total_Minutes, 60)
    VAR Total_Days = INT(Total_Hours/24)
    VAR Remaining_Hours = MOD(Total_Hours, 24)

    VAR Formatted_Days = IF(Total_Days > 0, Total_Days & " d ")
    VAR Formatted_Hours = IF(Remaining_Hours > 0, Remaining_Minutes & " h ")
    VAR Formatted_Minutes = IF(Remaining_Minutes > 0, Remaining_Minutes & " m")
    VAR Result = Formatted_Days & Formatted_Hours & Formatted_Minutes

    RETURN
            Result

The issue with the original answer was because the person put in the "Days in Current Status" measure "" for the value_if_false in the formatted IF statements. Once I removed the value_if_false everything started working correctly.

Results