powerbi - Power BI Line Chart: How to show bug trend by plotting "Active" and "Resolved" sta

I'm trying to create a line chart in Power BI to visualize the number of bugs created and resolved over time. My dataset includes a list of bugs with Created Date and Resolved Date fields.

I want the chart to look like this (example from Excel):

What I’ve Tried:

1. Chart Setup:
   • X-axis: Created Date
   • Values: Count of Work Item ID

But it plots only like this? What I am missing? How to get Week1, Week2....?

Update

As suggested DataTable is created

Added the Visualization

But graph is not showing any data!

Few more Updates

I'm trying to create a line chart in Power BI to visualize the number of bugs created and resolved over time. My dataset includes a list of bugs with Created Date and Resolved Date fields.

I want the chart to look like this (example from Excel):

What I’ve Tried:

1. Chart Setup:
   • X-axis: Created Date
   • Values: Count of Work Item ID

But it plots only like this? What I am missing? How to get Week1, Week2....?

Update

As suggested DataTable is created

Added the Visualization

But graph is not showing any data!

Few more Updates

• powerbi

Share Improve this question Follow edited Feb 4 at 16:12 kudlatiger asked Feb 4 at 11:35 kudlatigerkudlatiger 3,2881212 gold badges5555 silver badges111111 bronze badges 1

• can you create a table visualization with this Data. And can you check if there is a connection between the Tables in the Modelview? If so, can you delete this connetion – Moosli Commented Feb 5 at 7:51

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

If you don't already have a date table, create one using DAX. This helps group your data by week. Go to Modeling > New Table (I tried to read the column names based on your screenshot, but maybe you have to adjust them):

DateTable = ADDCOLUMNS(
    CALENDAR(MIN(BugData[Created Date]), MAX(BugData[Resolved Date])),
    "YearWeek", FORMAT([Date], "YYYY \Week ") & FORMAT(WEEKNUM([Date]), "00")
)

This creates a continuous date range from the earliest created bug to the latest resolved bug. After that, relate the DateTable with your bug table.

Once the DateTable is connected, create two DAX measures:

Measure 1: count of bugs created per week

Bugs_Created_Week = 
VAR CurrentWeek = SELECTEDVALUE(DateTable[YearWeek])
RETURN CALCULATE(
    COUNT(BugData[Work Item ID]), 
    FILTER(BugData, BugData[Created Date] IN VALUES(DateTable[Date]))
)

Measure 2: count of bugs resolved per week

Bugs_Resolved_Week = 
VAR CurrentWeek = SELECTEDVALUE(DateTable[YearWeek])
RETURN CALCULATE(
    COUNT(BugData[Work Item ID]), 
    FILTER(BugData, BugData[Resolved Date] IN VALUES(DateTable[Date]))
)

After that, update your Power BI line chart

• X-Axis → DateTable[YearWeek] (instead of "Created Date")
• Y-Axis → Add both: Bugs_Created_Week, Bugs_Resolved_Week