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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
samuelnh
Frequent Visitor

Remove future values for running total

Hi all

 

I want to create a running total chart for project planning showing the amount of work my team has done compared to budget. I have used the running total quick measure and have a date table.

 

However, the charts I've created show a flat line for the future. The easiest to show with a picture:

 

Chart.png

I want to be rid of the flat horizontal lines in this. Should I change my measure or is there a way to do this with the chart itself?

 

The measure formula is as follows:

 

value_scheduled running total in Month_Year = 
CALCULATE(
	SUM('timesheets 16-04 to 18-03'[value_scheduled]),
	FILTER(
		ALLSELECTED('Dates'[Month_Year]),
		ISONORAFTER('Dates'[Month_Year], MAX('Dates'[Month_Year]), DESC)
	)
)

 

Data Here:

https://ufile.io/5twet

1 ACCEPTED SOLUTION

Hi,

 

Try this for value_scheduled running total in Month_Year

 

=if(ISBLANK(SUM('timesheets 16-04 to 18-03'[value_scheduled])),BLANK(),CALCULATE(
	SUM('timesheets 16-04 to 18-03'[value_scheduled]),
	FILTER(
		ALLSELECTED('Dates'[Month_Year]),
		ISONORAFTER('Dates'[Month_Year], MAX('Dates'[Month_Year]), DESC)
	)
))

Try this forvalue_worked running total in Month_Year

 

=if(ISBLANK(SUM('timesheets 16-04 to 18-03'[value_worked])),BLANK(),CALCULATE(
	SUM('timesheets 16-04 to 18-03'[value_worked]),
	FILTER(
		ALLSELECTED('Dates'[Month_Year]),
		ISONORAFTER('Dates'[Month_Year], MAX('Dates'[Month_Year]), DESC)
	)
))

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

 

Share the download link of your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi - do you mean the link to the data that I'm using, etc?

 

Thanks,

 

Sam

Yes. The download link of your .pbix file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

It contains commerically sensitive information. Is it standard to share all of the data one is working on?

Dummy the dataset.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Edited and added above.

Hi,

 

Try this for value_scheduled running total in Month_Year

 

=if(ISBLANK(SUM('timesheets 16-04 to 18-03'[value_scheduled])),BLANK(),CALCULATE(
	SUM('timesheets 16-04 to 18-03'[value_scheduled]),
	FILTER(
		ALLSELECTED('Dates'[Month_Year]),
		ISONORAFTER('Dates'[Month_Year], MAX('Dates'[Month_Year]), DESC)
	)
))

Try this forvalue_worked running total in Month_Year

 

=if(ISBLANK(SUM('timesheets 16-04 to 18-03'[value_worked])),BLANK(),CALCULATE(
	SUM('timesheets 16-04 to 18-03'[value_worked]),
	FILTER(
		ALLSELECTED('Dates'[Month_Year]),
		ISONORAFTER('Dates'[Month_Year], MAX('Dates'[Month_Year]), DESC)
	)
))

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Perfect thanks - works an absolute charm.

Hi,

 

If my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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