cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aaumond Frequent Visitor
Frequent Visitor

Calculate TimeInStatus

Hi,

 

I retrieve data from my JIRA projects in PowerBI via the REST API services that JIRA provides.


On each ticket, I retrieve the history of the actions that took place, including status changes. For this case (status changes), I know the original status (Change.fromString), the final status (Change.toString) and the datetime of the status change.

 

So, the data is looks like this: 
2018-05-27 20_17_26-Téléchargements.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

From these data, I try to know the time spent in "Demande imprécise" status.

 

For example, if I consider the "11419" ID ticket, here are the periods of time in "Demande imprécise" status and the duration :

- 03/05/18 5:06 PM - 03/05/18 5:06 PM  ==> 14 s
- 03/05/18 5:34 PM - 03/05/18 5:37 PM ==> 3 m
- 03/05/18 5:38 PM - 03/05/18 5:40 PM ==> 2 m
Total: 5 m 14 s

 

I am a little lost to make theses operations, have you already realized that? If yes, how?

 

Tahnks in advance.

 

Best regards,

Anthony

5 REPLIES 5
Community Support Team
Community Support Team

Re: Calculate TimeInStatus

Hi @aaumond

You could write calculated columns with DAX as follows step by step.
date ship down =
CALCULATE (
    MAX ( [Change date] ),
    FILTER (
        ALL ( Sheet1 ),
        [Index]
            = EARLIER ( [Index] ) - 1
            && MOD ( [Index], 2 ) = 0
            && [ID ticket] = EARLIER ( [ID ticket] )
    )
)
second =
IF (
    DATEDIFF ( [date ship down], [Change date], SECOND ) < 60,
    DATEDIFF ( [date ship down], [Change date], SECOND )
)
Column =
CONCATENATE (
    INT (
        MOD (
            CALCULATE (
                SUM ( [second] ),
                FILTER ( ALL ( Sheet1 ), [ID ticket] = EARLIER ( [ID ticket] ) )
            ),
            3600
        )
            / 60
    ),
    "m"
)
    & CONCATENATE (
        INT (
            MOD (
                MOD (
                    CALCULATE (
                        SUM ( [second] ),
                        FILTER ( ALL ( Sheet1 ), [ID ticket] = EARLIER ( [ID ticket] ) )
                    ),
                    3600
                ),
                60
            )
        ),
        "s"
    )
1.png
Or you can write a formula to calculate the total time period per ID ticket.
Column 2 =
VAR dateshipdown =
    CALCULATE (
        MAX ( [Change date] ),
        FILTER (
            ALL ( Sheet1 ),
            [Index]
                = EARLIER ( [Index] ) - 1
                && MOD ( [Index], 2 ) = 0
                && [ID ticket] = EARLIER ( [ID ticket] )
        )
    )
VAR second =
    IF (
        DATEDIFF ( [date ship down], [Change date], SECOND ) < 60,
        DATEDIFF ( [date ship down], [Change date], SECOND )
    )
RETURN
    CONCATENATE (
        INT (
            MOD (
                CALCULATE (
                    SUM ( [second] ),
                    FILTER ( ALL ( Sheet1 ), [ID ticket] = EARLIER ( [ID ticket] ) )
                ),
                3600
            )
                / 60
        ),
        "m"
    )
        & CONCATENATE (
            INT (
                MOD (
                    MOD (
                        CALCULATE (
                            SUM ( [second] ),
                            FILTER ( ALL ( Sheet1 ), [ID ticket] = EARLIER ( [ID ticket] ) )
                        ),
                        3600
                    ),
                    60
                )
            ),
            "s"
        )
 
Best Regards
Maggie
aaumond Frequent Visitor
Frequent Visitor

Re: Calculate TimeInStatus

Hi @v-juanli-msft,

 

Thanks for your reply.

 

Do you think it's possible to do this in Power Query rather than DAX ?

 

Regards,

Anthony

 

 

pa Visitor
Visitor

Re: Calculate TimeInStatus

Hello,

 

i'm looking for this request. How do have make to retrieve the history of the actions, including status changes exactly like your screenshot ?

 

Thank you.

VivianX Frequent Visitor
Frequent Visitor

Re: Calculate TimeInStatus

I have some question as @pa 

How do have make to retrieve the history of the actions, including status changes exactly like your first screenshot ? @aaumond 

 

ao352 Frequent Visitor
Frequent Visitor

Re: Calculate TimeInStatus

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,910)