Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.