Help with line and stacked column chart for running totals

[MikeSwe]

I am struggling to create the graph that I want and hope that someone can help me.

The goal is to create a Lined and Stacked column chart containing bars with a summary of issues created per year and in the same chart a line that shows the number of unresolved/open issues per year as a running total.

The table is called "JiraData" and I have a few key columns in the table that I am trying to use for the graph.

- "Created" Contains the date when the issue was created.

- "Resolved" Contains the date of when the issue was resolved, if not yet resolved this column is blank.

- "Issue Key" Contains a unique value per issue.

When using "Year" from the date hierarchy for the X-axis and "Count of Issue Key" for the Y-axis it correctly show the bars with a summary of issues created per year. 

I have also created a new column called "Unresolved" which is set to "1" for unresolved issues and "0" for resolved.

What should I type in the measure to calculate the number of open issues per year as a running total and use that for my Line Y-axis?

If needed I can create a separate Date table, create new columns or make any modification necessary to best accomplish the above.

Thanks!

[Daniel29195 · Accepted solution]

@MikeSwe 

assuming, your chart have date column in it : 

to create a running total. use this  

measure = 

var maxdate  = max(date_table[date_column])
var res = 

calculate (
sum(table_name[col_name]),
all(date_table) , 
date_table[date_column] < = maxdate ) 

return res

option2 :  

this will take into consideration the period selected, and will start your running total base on your selection : 

measure = 

var maxdate  = calculate( max(date_table[date_column]))

var mindate = calculate( min (date_table[date_column] ,  allselected( date_table[date_column]) ) 
var res = 

calculate (
sum(table_name[col_name]),
all(date_table) , 
date_table[date_column] < = maxdate ,

date_table[date_column] >= mindate ,

) 

return res

tell me if it works out for you . 

if you have any concerns feel free to share them so i can assist you  . 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍