Issue with line graphs showing the cumulative count in power bi

Question

I am trying to show a line graph in power BI report for cumulative count of number of issues opened and resolved on each day. I have a table as below with the details of the list of issues. If an issue is not yet resolved, the resolved date field is empty. I have created the below measures to get the cumulative counts.

  # Get the total count of closed issues 
 total closed issues = COUNTROWS(FILTER(Issues,Issues[status] = "Closed" || Issues[status] = "Declined"))

#Get the total count of open issues    
total issues = COUNTROWS(FILTER(Issues,Issues[status] <> "Closed" && Issues[status] <> "Declined"))

#Cumulative count of open issues
running total = CALCULATE([total issues],DATESINPERIOD(Issues[Created Date],MAX(Issues[Created Date]),-365,DAY))

#cumulative count of closed issues    
running closed total = CALCULATE([total closed issues],DATESINPERIOD(Issues[Resolved Date],MAX(Issues[Resolved date]),-365,DAY))

But I see issues in the linegraph created, it is showing correct if graph is taken as created date vs running total and resolved date vs running closed total. But combination of those two is giving incorrect results especially the running closed total is not showing the cumulative count instead showing only the number of issues closed on each day. What am I missing here?

Rajat Anand Rajat Anand · Accepted Answer · 2020-06-13T09:58:51

The issue is because both the measures are running on two different columns of created date and resolved date.

You can duplicate the table and keep only issue id and resolution date in the new second table. Add a new thrid table Table = CALENDAR(DATE(2019,1,1), TODAY())

running total = CALCULATE([total issues],DATESINPERIOD('Table'[Date],MAX('Table'[Date]),-365,DAY))

running closed total = CALCULATE([total closed issues],DATESINPERIOD('Table'[Date],MAX('Table'[Date]),-365,DAY))

The 'total closed issues' and 'running closed total' measure are to be moved to new resolved table

OR YOU CAN ALSO TRY THIS ALONE IF THIS VISULAIZATION IS ALSO WHAT YOU NEED

You can modify the measure as -

running closed total = CALCULATE([total closed issues],DATESINPERIOD(Issues[Created Date],MAX(Issues[Created Date]),-365,DAY))

This will give you the line graph of an issue that was created on a particular date and is resolved irrespective of the resolution date.

Issue with line graphs showing the cumulative count in power bi

1 Answers