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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
lmperplies
Regular Visitor

Calculate Decreasing Total minus total completed

Hi there - 

 

I am trying to build a graph that shows the decreasing number of items that need to be completed by date. I need to show a graph that show the decreasing total by the x completed over time. My starting total is 2456, 10/31/23. So on 11/1/23 the total should be 2,297 (2456 - 159) then on 11/2/23 the total should be 2,256 (2297 - 41) and 11/3 the total should be 2253 (2256 - 3) and so on. 

 

The x column is a measure field 

x =
var
TCBkr =
    CALCULATE(COUNT('Query - All'[SUBCLASS]), FILTER('Query - All','Query - All'[SUBCLASS] = "Breaker" && 'Query - All'[data.Status] = "Closed"))
Return
    TCBkr
 
The data.End is a date field

 

lmperplies_0-1700515271446.png

Thank you for your assistance.

1 ACCEPTED SOLUTION

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.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi @lmperplies ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
lmperplies
Regular Visitor

@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:

lmperplies_1-1700573974823.png

This is the result I am seeing:

lmperplies_0-1700573592318.png

 

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
Resident Rockstar
Resident Rockstar

It 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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors