Report to show last 7 days and last 30 days issues created and resolved

[Aman-K]

Hi there,

I am new to Power BI so hopefully anyone can help with my query as below:

I have done Power BI and Jira integartion using Rest API and have all the data in one query . The data has been formatted and column renamed as required . I have DateCreated column which stores date when the issue was created and ResolutionDate column which shows the date when the issue was resolved . 

I want to create a bar chart that will show issues created and resolved in last 7 days and last 30 days . 

Can you let me know the steps? Do I need to create another query for this and report based on that query? I haven't worked on M language or DAX functions etc.. so any help will be much appreciated . 

Many Thanks,

Aman

[Aman-K]

Thanks Amit for this useful information. I have managed to create a Date table as you described in the video so I have now Date,Month Year,Month Year Order,Year  . I have added a new measure named "TotalIssues" which shows the total number of issues in each Month Year column(using formula - TotalIssues = COUNT(AllJiraData[IssueID])

. I need to add another measure "ResolvedIssues" will calculate total resolved issues in each Month Year. I tried using the below measure but it shows same value (Total for all issues) in each Month Year which is not correct 

Resolved Issues = CALCULATE(COUNTROWS('AllJiraData'), FILTER(ALL('AllJiraData'),NOT(ISBLANK('AllJiraData'[ResolutionDate]))))

Can you please help me in this?

Many Thanks,

Aman

[amitchandak]

@Aman-K , for open and closed you can follow the common date table, and use userelationship to work with inactive join

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

If you need a trend you can follow

Need of an Independent Table in Power BI - Exclude: https://youtu.be/lOEW-YUrAbE

Make sure you use measures already created with userelationship

else use Rolling Days Formula: https://youtu.be/cJVj5nhkKBw

[Aman-K]

Hi ,

I have created a Date table so I now have Date,Month Year,Month Year Order,Year  . I have added a new measure named "TotalIssues" which shows the total number of issues in each Month Year column(using formula - TotalIssues = COUNT(AllJiraData[IssueID])

. I need to add another measure "ResolvedIssues" which will calculate total resolved issues in each Month Year. I tried using the below measure but it shows same value (Total for all issues) in each Month Year which is not correct 

Resolved Issues = CALCULATE(COUNTROWS('AllJiraData'), FILTER(ALL('AllJiraData'),NOT(ISBLANK('AllJiraData'[ResolutionDate]))))

Can anyone please help me how can I find total resolved issues for each day and month ?

Many Thanks,

Aman

Many Thanks,

Aman

[Ashish_Mathur]

Hi,

Create an inactive relationship between the resolution date and Calendar Table.  Write this measure

Issues resolved = calculate([Total issues],userelationship('AllJiraData'[ResolutionDate],Calendar[date]))

To your visual, ensure that Year and Month name are dragged from the Calendar Table.

Hope this helps.

[Aman-K]

Hi @Ashish_Mathur , Thanks so much. It worked fine . Is there anyway I can add a filter so it doesn't count the records where ResolutionDate is null. At the moment it's showing 368 null records as below. 

[Ashish_Mathur]

Hi,

Either filter the visual with the criteria of Resolution Date <> blank or use this modified measure

=calculate([Total issues],'AllJiraData'[ResolutionDate]<>blank(),userelationship('AllJiraData'[ResolutionDate],Calendar[date]))

Hope this helps.

[Aman-K · Accepted solution]

Thanks a lot @Ashish_Mathur , This has now been resolved 👍