Trouble displaying a monthly running total by fiscal year

[TimQ]

I'm having trouble displaying a monthly running total calculation by fiscal year. When I create a running total by month and display it by calendar year, it works fine. But when I try to create a similar measure based on custom fiscal year/month fields, the months with blank values appear blank instead of taking the prior month's value. Below are my correctly displayed table (left) showing the monthly running total by calendar year using the table's date field and the incorrectly displayed table (right) showing the monthly running total by fiscal year:

My measure for calendar year running total is this:

Cumulative Total Project Encumbrance = 
CALCULATE(
	[Total Project Encumbrance],
	FILTER(
		CALCULATETABLE(
			SUMMARIZE(
				'Contract Award',
				'Contract Award'[Contract Award Date].[MonthNo],
				'Contract Award'[Contract Award Date].[Month]
			),
			ALLSELECTED('Contract Award')
	),
	ISONORAFTER(
	'Contract Award'[Contract Award Date].[MonthNo], MAX('Contract Award'[Contract Award Date].[MonthNo]), DESC,
	'Contract Award'[Contract Award Date].[Month], MAX('Contract Award'[Contract Award Date].[Month]), DESC
		)
	)
)

My measure for running total by fiscal month is this:

Total Project Encumbrance running total in Fiscal Month Abbr = 
CALCULATE(
	[Total Project Encumbrance],
	FILTER(
		CALCULATETABLE(
			SUMMARIZE(
				'Contract Award',
				'Contract Award'[Fiscal Month Num],
				'Contract Award'[Fiscal Month Abbr]
			),
			ALLSELECTED('Contract Award')
		),
		ISONORAFTER(
			'Contract Award'[Fiscal Month Num], MAX('Contract Award'[Fiscal Month Num]), DESC,
			'Contract Award'[Fiscal Month Abbr], MAX('Contract Award'[Fiscal Month Abbr]), DESC
		)
	)
)

Where

Fiscal Month Num = IF(MONTH('Contract Award'[Contract Award Date]) <= 6, 
MONTH('Contract Award'[Contract Award Date]) + 6, MONTH('Contract Award'[Contract Award Date]) -6)

and

Fiscal Month Abbr = if('Contract Award'[Fiscal Month Num] = 1,"Jul",
if('Contract Award'[Fiscal Month Num] = 2,"Aug",
if('Contract Award'[Fiscal Month Num] = 3,"Sep",
if('Contract Award'[Fiscal Month Num] = 4,"Oct",
if('Contract Award'[Fiscal Month Num] = 5,"Nov",
if('Contract Award'[Fiscal Month Num] = 6,"Dec",
if('Contract Award'[Fiscal Month Num] = 7,"Jan",
if('Contract Award'[Fiscal Month Num] = 8,"Feb",
if('Contract Award'[Fiscal Month Num] = 9,"Mar",
if('Contract Award'[Fiscal Month Num] = 10,"Apr",
if('Contract Award'[Fiscal Month Num] = 11,"May",
if('Contract Award'[Fiscal Month Num] = 12,"Jun",BLANK()))))))))))))

How can I correctly display a running total without blank values?

PBIX file is linked here.

Thanks.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[TimQ]

Looks great - thanks @Ashish_Mathur!

[Ashish_Mathur]

You are welcome.

[TimQ]

Hi Ashish,

Thanks - I updated my original post at the bottom with a link to the file.

Tim

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.