Forum Discussion
Calculate Decreasing Total minus total completed
You can use the following modified measure:
DecreasingTotal =
VAR InitialTotal = 2456
VAR CurrentDate = MAX('Query - All'[data.End])RETURN
InitialTotal -
CALCULATE(
COUNTROWS(FILTER('Query - All', 'Query - All'[data.End] <= CurrentDate && 'Query - All'[SUBCLASS] = "Breaker" && 'Query - All'[data.Status] = "Closed"))
)This measure calculates the initial total minus the count of completed items up to the current date. It uses the FILTER function to filter the data for each date, and the condition 'Query - All'[data.End] <= CurrentDate ensures that only the items completed on or before the current date are considered.
Make sure to replace 'Query - All' with the actual name of your table. Add this measure to your visualizations, and it should display the decreasing total of items over time as you specified.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
123abc This is a great start but not getting the results I am looking for. The measure you suggested above gets me closer but it returns the daily completed total minus the base total minus the date differnce which is counting (1,2,3,4...). What I need to figure out is how to calculate the total of the dates then minus that from the base total.
Example:
This is the result I am seeing:
- 123abc
Community Champion
You can use the following modified measure:
DecreasingTotal =
VAR InitialTotal = 2456
VAR CurrentDate = MAX('Query - All'[data.End])RETURN
InitialTotal -
CALCULATE(
COUNTROWS(FILTER('Query - All', 'Query - All'[data.End] <= CurrentDate && 'Query - All'[SUBCLASS] = "Breaker" && 'Query - All'[data.Status] = "Closed"))
)This measure calculates the initial total minus the count of completed items up to the current date. It uses the FILTER function to filter the data for each date, and the condition 'Query - All'[data.End] <= CurrentDate ensures that only the items completed on or before the current date are considered.
Make sure to replace 'Query - All' with the actual name of your table. Add this measure to your visualizations, and it should display the decreasing total of items over time as you specified.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.