Jira/PowerBI data - historical tracking (sample file included)

[BSands]

Hello,

I'm looking to create some reports based on Jira data that I have pulled into PowerBI. I have created a static sample version of the table I am using. In the end I want to be able to create a report that shows at any given date in history (grouped by month ideally), how many of the Jira tickets were in status Backlog. 

There are multiple rows per Key, with a many:1 relationship to the master Issue table (not included in this sample data). I want to be able to say that on April 5 2025 there were 110 issues in backlog status, and then on april 6 if 5 are added then it will show 115, and april 7 if 10 are removed it will show 105 etc.

The only success I have had so far is in creating a table that shows the number of issues added to the backlog status on any given date, but I want a running total essentially.

Thanks,

Beth

Google doc with sample history data 

[Irwan]

hello @BSands 

to calculate running total, you might want to try this DAX

i am only using 'History New Value End (date only)' as a filter but you can add filter to your need.

Hope this will help.

Thank you.

[BSands]

Thank you for this, but I'm a bit unclear what that measure is counting? Is it counting all instances of History Field for a date range? And how is it doing the running sum? From the bar chart I created it looks like it's only increasing, not removing tickets from the count as they pass the History New Value End Date. 

I'd really appreciate walking me through the thought process of the measure, as there are other values in this history table I'd like to do something similar for (ie. tickets assigned to a specific person over time)

[Irwan]

hello @BSands 

it measure running total before maximum date.

the filter only date so it is a little bit off.

i assumed there is a condition to define backlog but seems there is no description on how to define backlog from your sample data.

therefor you can add more filter as your need.

otherwise, please describe more about your need.

Thank you.

[BSands · Accepted solution]

Thank you, I was able to use some of what you created combined with what I found in this video about Employee Tenure to create what I needed.

In case anyone is looking here in the future:
Found in the solution for this thread:
https://community.fabric.microsoft.com/t5/Desktop/Filter-Historical-Data/m-p/3480044
Youtube link
Master HR Analytics in Power BI: Employee Tenure Analysis & Userelationship | Active Employees