Time Tracking on Jira Report

[Anonymous]

Hi,

Im trying to build a matrix or table for Time Tracking and stuck at the calcuations part. Below is the screenshot of what I am looking to achieve.

I have already connected JIRA to PBI and it has all the fields mentioned here except Accuracy.

Below are my concerns which I want solution on.

1. The Original Estimate Column,Estimated Time Remaining, Time Spent all these columns have data in hours, mins, days etc. How do I achieve the same result in PowerBi. 

2.Accuracy column calculates Orginal Estimate - Time Spent so if its equal then its on track else it should show in Days, hours, mins 

[v-kkf-msft]

Hi @Anonymous ,

What is the data format you imported from JIRA?

Is it date/time format or text format?

Could you please share a screenshot of the time data columns?

I don't know the specific format of your data, but the Duration in Power Qurey should help you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks Winniz a ton to respond to my query.

Il explain the situation in detail here below.

Steps.

1. I have connected JIRA to PowerBi via PowerBiPro connector on JIRA where we can add all the fields present and also mention the project it is a part of so for eg if you see the screenshot I shared which has columns like (Key, Summary, Original Estimate etc)

2. I copy the URL and use the PowerBi connecter OData Feed to connect and pull the fields required.

3. After import this is how the numbers look like

Its imported as its on JIRA..so the Original Estimate for one issue (INFOPS-1) is 2 hours so in Jira it shows as 2h and if I Import that field into PowerBi through the connector it shows as 2 but in decimal.

I dont know if the Duration option would help because if you check my screenshot of JIRA the Original Estimate Column has data in Hours, Minutes, Days so if I just mark Original Estimate Column as Hours or Days as shown in your reply then it would not be as per my expected result.

Let me know if you need any further information from my end.

Thanks in advance

[v-kkf-msft]

Hi @Anonymous ,

It cannot set multiple units in the same column in Power BI.

Maybe you can try to create calculated column or measure to achieve.

According to your picture, I guess the imported data should be in hours. Then I use the following format to create a calculated column. Does this match the output you expect?

Column = 
var day = TRUNC('Table'[test]/24)
var hour = TRUNC('Table'[test] - day*24)
var minutes = ( 'Table'[test] - TRUNC('Table'[test]) ) * 60
return 
    IF(day > 0, day&"d ") & IF(hour > 0, hour&"h ") & IF(minutes > 0, minutes&"m")

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This looks perfectly fine according to me and think this should work.

Couple of queries.

1. How do I achieve the accuracy column calculation like if Accuracy column calculates Orginal Estimate - Time Spent so if its equal then it should show on track else it should show in data Days, hours, mins

2. In the JIRA screenshot there is a progress bar that shows progress and accuracy, is there a way we can show that as well. Apologies, I hope im not asking too much here. 

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Try the following formula:

Accuracy = 
var _value = 'Sample'[Original Estimate] - 'Sample'[Time Spent]
var day = TRUNC(_value/24)
var hour = TRUNC(_value - day*24)
var minutes = ( _value - TRUNC(_value) ) * 60
var t = IF(day <> 0, day&"d ") & IF(hour <> 0, hour&"h ") & IF(minutes <> 0, minutes&"m")
return 
    IF( _value = 0,  "on track", t)

Original = 
var day = TRUNC('Sample'[Original Estimate]/24)
var hour = TRUNC('Sample'[Original Estimate] - day*24)
var minutes = ( 'Sample'[Original Estimate] - TRUNC('Sample'[Original Estimate]) ) * 60
return 
    IF(day > 0, day&"d ") & IF(hour > 0, hour&"h ") & IF(minutes > 0, minutes&"m")

TimeSpentColumn = 
var day = TRUNC('Sample'[Time Spent]/24)
var hour = TRUNC('Sample'[Time Spent] - day*24)
var minutes = ( 'Sample'[Time Spent] - TRUNC('Sample'[Time Spent]) ) * 60
return 
    IF(day > 0, day&"d ") & IF(hour > 0, hour&"h ") & IF(minutes > 0, minutes&"m")

I don't know which columns are used to calculate the data in the red part of the icon below. 

So I create measures according to my guess:

Process = DIVIDE( SUM('Sample'[Original Estimate]), SUM('Sample'[Time Spent]) )

AccuracyMeasure = DIVIDE( SUM('Sample'[Time Spent]) - SUM('Sample'[Original Estimate]), SUM('Sample'[Original Estimate]) )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks a ton for your answers. Il put these in place and check if it works.

I believe it should work.

Thanks again. Really appreciate your help on them.

[amitchandak]

@Anonymous , is it an import mode of App?

because in case of import mode create a date from date time and join that with date table to get time intelligence

Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.