Remove future values for running total

[samuelnh]

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:

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

[Ashish_Mathur]

Hi,

Share the download link of your file.

[samuelnh]

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

Thanks,

Sam

[Ashish_Mathur]

Yes. The download link of your .pbix file.

[samuelnh]

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

[Ashish_Mathur]

Dummy the dataset.

[samuelnh]

Edited and added above.

[Ashish_Mathur · 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)
	)
))

[samuelnh]

Perfect thanks - works an absolute charm.

[Ashish_Mathur]

Hi,

If my reply helped, please mark it as Answer.