Create a running total column from a measure ( same table or new summarized table )

[ANP]

I Have a table as below

Source_Table:

Issue id	Logged date	Resolved date

Date_Calendar:

Date	Month-Year	Month year order

I could create  No_Of_Logged_defects and No_Of_Resolved_defects measure by using separate relationship with date table and calculating the distinctcount. and I am able to show 'month year' on x axis and these above two measures on the y axis and working fine. My problem starts here that I want to show number of open defects in that month which is actually 'Running total of No_Of_logged_defects'  minus 'Running total of No_of_Resolved_defects"  till that month..  how can I show that. If I use 'Running total quick measure' for both No_of_logged_defects and No_of_resolved_defects and calculate another measure for No_of_open_defects by substracting, it works fine but without any date filters ( if I enter dates in my date filter ) that running total starts from the filtered critieria which shows wrong number of open defects..) .. ideally I would have preferred a separate table which summarizes month year, no of logged defects, no. of resolved defects , no . of open defects in that standalone I can show them in front end easily.  Please help. 

[123abc]

Create a new table that summarizes the data:

You can create a new table by going to the Power BI Data view and selecting "Model" in the top menu. Then, click on "New Table" and enter the following DAX formula to create a summary table:

SummaryTable =
SUMMARIZE (
Source_Table,
Date_Calendar[Month-Year],
"No_Of_Logged_Defects", [No_Of_Logged_Defects],
"No_Of_Resolved_Defects", [No_Of_Resolved_Defects]
)

1. This formula summarizes the data by month-year and includes the counts of logged and resolved defects for each month-year.

2. Create a measure for the running total of open defects:

   Now, you can create a measure to calculate the running total of open defects using the summary table you just created. Here's a DAX formula for the measure:

Running_Total_Open_Defects =
VAR SummaryTable =
FILTER ( ALL ( SummaryTable ), [Month-Year] <= MAX ( Date_Calendar[Month-Year] ) )
VAR RunningTotalLoggedDefects =
SUMX ( SummaryTable, [No_Of_Logged_Defects] )
VAR RunningTotalResolvedDefects =
SUMX ( SummaryTable, [No_Of_Resolved_Defects] )
RETURN
RunningTotalLoggedDefects - RunningTotalResolvedDefects

1. This measure calculates the running total of open defects up to the selected month-year in the Date_Calendar table. It first filters the SummaryTable to include only rows up to the selected month-year, then calculates the running totals of logged and resolved defects, and finally subtracts the resolved defects from the logged defects to get the open defects.

Now, you can use the "Running_Total_Open_Defects" measure in your visuals along with the "Month-Year" from the Date_Calendar table to show the running total of open defects by month-year. This should work correctly with date filters applied to your visuals since it's based on the summary table.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.