Adding (and subtracting) Value Counts Over Time

[benice25]

Good Afternoon PowerBi Community!

I'm new to PowerBi & DAX, so I'm not really sure where to start to solve this, and I think I just need some help from all you wonderful folks out here.

What I'm Trying to Do

I received a request to show the number of bugs (from a Jira table) for each project over time.

I'm able to pull the bugs that were opened and closed on a given day, but I don't know how to make trendlines that shows this...

Day 1	10 Bugs Opened	5 Bugs Closed
Day 2	5 Bugs Opened (so 15 total)	5 Bugs Closed (so 10 closed total)
Day 3	20 Bugs Opened (so 35 opened total)	10 Bugs Closed (so 20 closed total)
Etc...

Ideally that would be 4 lines, one line that shows bugs opened that day, one line that shows bugs closed that day, one line that shows the new total opened bugs, and one line that shows new total closed bugs.

Limitations

I would like to do this with DAX so I don't have to add yet another table and a connection via group by PowerQuery.

Bonus

If there is a way to do one more calculation that would be amazing, and that would be to analyze the average rate that these bugs open and close in a given day, and then divide the total number of bugs by that average to better understand how much work is remaining to finish out the rest of the bugs (given if no new bugs are created). 

[v-luwang-msft]

Hi @benice25 ，

Is the original template data available, also according to what you provided above, why is the closure 25? (5+5+10?)

Best Regards

Lucien

[benice25]

Hi @v-luwang-msft ,

Yah that should be 20, and not 25, I'm just guessing I couldn't math that day. 🙂 I'll go edit it.

The data isn't available given goverment regulations in the power industry or I would totally provide it. 

Also something that is helpful, I did find out how to do the first calculation, but I am having trouble figuring out how to do it in reverse:

Here is the adding up version, just not sure the subtracting over time.

CALCULATE(
    SUM( 'Test Results Grouped By Date'[Test Cases Executed] ),
    FILTER( ALL( 'Test Results Grouped By Date'[EXECUTION_DATE] ), 
    'Test Results Grouped By Date'[EXECUTION_DATE] <= MAX( 'Test Results Grouped By Date'[EXECUTION_DATE] )
    )
)