cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aman-K
Helper III
Helper III

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

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

1 ACCEPTED SOLUTION

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

View solution in original post

7 REPLIES 7
Aman-K
Helper III
Helper III

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
Super User
Super User

@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



!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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. 

AmanK_0-1679822776237.png

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors