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.
Community ChampionIt looks like you want to create a measure in Power BI that calculates the decreasing total of items to be completed over time based on a starting total of 2456 on 10/31/23. You also want to use the 'Query - All' table with a filter for 'Breaker' subclass and 'Closed' status.
You can create a new measure for the decreasing total using DAX (Data Analysis Expressions). Here's an example measure you can use:
DecreasingTotal =
VAR StartDate = DATE(2023, 10, 31) -- Set the starting date
VAR BaseTotal = 2456 -- Set the starting total
VAR CompletedToday = CALCULATE(COUNT('Query - All'[SUBCLASS]), 'Query - All'[SUBCLASS] = "Breaker" && 'Query - All'[data.Status] = "Closed" && 'Query - All'[data.End] = MAX('Query - All'[data.End]))
VAR CurrentDate = MAX('Query - All'[data.End])
VAR DaysDifference = DATEDIFF(StartDate, CurrentDate, DAY)
RETURN
BaseTotal - DaysDifference - CompletedToday
This measure calculates the decreasing total by subtracting the difference in days between the current date and the starting date from the base total. It also subtracts the count of completed items for the specific subclass on the current date.
Make sure to replace 'data.End' with the actual column name in your 'Query - All' table that represents the date. Also, adjust the date format in the DATE function according to your date format.
You can use this measure in your graph with the 'x' column as the date field and the 'DecreasingTotal' as the value field. This should give you the desired graph showing the decreasing total of items over time.
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.