Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ANP
Frequent Visitor

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

I Have a table as below

Source_Table:

Issue idLogged dateResolved date
   

 

Date_Calendar:

DateMonth-YearMonth 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. 

1 REPLY 1
123abc
Community Champion
Community Champion

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.