Expression is showing null when selecting the Month field

[bhaveshp90]

Hello, 

The Expression MTD is showing the correct value as shown in the below image. But when a Month field is selected it is becoming a null value. 

When September-2018 is selected it is showing null value

What I am missing in my MTD expression, attached is the file for reference. 

Aggr(If(Only({1} Project_Number) = Above(TOTAL Only({1} Project_Number)),
(
(
	if(Cost_Code=8,Sum({<Cost_Code={8}>}Prior_Updated_OEC_Budget),
	if(Cost_Code=10,Sum({<Cost_Code={10}>}Prior_Updated_OEC_Budget),
	if(Cost_Code=12,Sum({<Cost_Code={12}>}Prior_Updated_OEC_Budget),
	Sum({<Cost_Code={8,10,12}>}Prior_Updated_OEC_Budget))))
	-
	if(Cost_Code=8,Sum({<Cost_Code={8}>}Prior_Forecast_Cost_to_Complete),
	if(Cost_Code=10,Sum({<Cost_Code={10}>}Prior_Forecast_Cost_to_Complete),
	if(Cost_Code=12,Sum({<Cost_Code={12}>}Prior_Forecast_Cost_to_Complete),
	Sum({<Cost_Code={8,10,12}>}Prior_Forecast_Cost_to_Complete))))
)
*
(
	If(Cost_Code=8, Sum({<Cost_Code={8}>}POC)/100
	-
	Above(TOTAL Sum({<MonthYear,Year,YearMonth,Cost_Code={8}>}POC)/100, Num(Month)),
	If(Cost_Code=10, Sum({<Cost_Code={10}>}POC)/100
	-
	Above(TOTAL Sum({<MonthYear,Year,YearMonth,Cost_Code={10}>}POC)/100, Num(Month)),
	If(Cost_Code=12, Sum({<Cost_Code={12}>}POC)/100
	-
	Above(TOTAL Sum({<MonthYear,Year,YearMonth,Cost_Code={12}>}POC)/100, Num(Month)),
	Sum({<Cost_Code={8,10,12}>}POC)/300
	-
	Above(TOTAL Sum({<MonthYear,Year,YearMonth,Cost_Code={8,10,12}>}POC)/300, Num(Month)))))	
)
)
- 
Alt(Above(TOTAL
((
	if(Only({<MonthYear, Snapshot_Date>}Cost_Code)=8,Sum({<MonthYear, Snapshot_Date,Cost_Code={8}>}Prior_Updated_OEC_Budget),
	if(Only({<MonthYear, Snapshot_Date>}Cost_Code)=10,Sum({<MonthYear, Snapshot_Date,Cost_Code={10}>}Prior_Updated_OEC_Budget),
	if(Only({<MonthYear, Snapshot_Date>}Cost_Code)=12,Sum({<MonthYear, Snapshot_Date,Cost_Code={12}>}Prior_Updated_OEC_Budget),
	Sum({<MonthYear, Snapshot_Date,Cost_Code={8,10,12}>}Prior_Updated_OEC_Budget))))
	-
	if(Only({<MonthYear, Snapshot_Date>}Cost_Code)=8,Sum({<MonthYear, Snapshot_Date,Cost_Code={8}>}Prior_Forecast_Cost_to_Complete),
	if(Only({<MonthYear, Snapshot_Date>}Cost_Code)=10,Sum({<MonthYear, Snapshot_Date,Cost_Code={10}>}Prior_Forecast_Cost_to_Complete),
	if(Only({<MonthYear, Snapshot_Date>}Cost_Code)=12,Sum({<MonthYear, Snapshot_Date,Cost_Code={12}>}Prior_Forecast_Cost_to_Complete),
	Sum({<MonthYear, Snapshot_Date,Cost_Code={8,10,12}>}Prior_Forecast_Cost_to_Complete))))
)
*
(
	If(Only({<MonthYear, Snapshot_Date>}Cost_Code)=8, Sum({<MonthYear, Snapshot_Date,Cost_Code={8}>}POC)/100
	-
	Above(TOTAL Sum({<MonthYear,Year,Snapshot_Date,YearMonth,Cost_Code={8}>}POC)/100, Num(Month)),
	If(Only({<MonthYear, Snapshot_Date>}Cost_Code)=10, Sum({<MonthYear, Snapshot_Date,Cost_Code={10}>}POC)/100
	-
	Above(TOTAL Sum({<MonthYear,Year,Snapshot_Date,YearMonth,Cost_Code={10}>}POC)/100, Num(Month)),
	If(Only({<MonthYear, Snapshot_Date>}Cost_Code)=12, Sum({<MonthYear, Snapshot_Date,Cost_Code={12}>}POC)/100
	-
	Above(TOTAL Sum({<MonthYear,Year,Snapshot_Date,YearMonth,Cost_Code={12}>}POC)/100, Num(Month)),
	Sum({<MonthYear, Snapshot_Date,Cost_Code={8,10,12}>}POC)/300
	-
	Above(TOTAL Sum({<MonthYear,Year, Snapshot_Date,MonthYear,YearMonth,Cost_Code={8,10,12}>}POC)/300, Num(Month)))))		
))
),0),
(
(
	if(Cost_Code=8,Sum({<Cost_Code={8}>}Prior_Updated_OEC_Budget),
	if(Cost_Code=10,Sum({<Cost_Code={10}>}Prior_Updated_OEC_Budget),
	if(Cost_Code=12,Sum({<Cost_Code={12}>}Prior_Updated_OEC_Budget),
	Sum({<Cost_Code={8,10,12}>}Prior_Updated_OEC_Budget))))
	-
	if(Cost_Code=8,Sum({<Cost_Code={8}>}Prior_Forecast_Cost_to_Complete),
	if(Cost_Code=10,Sum({<Cost_Code={10}>}Prior_Forecast_Cost_to_Complete),
	if(Cost_Code=12,Sum({<Cost_Code={12}>}Prior_Forecast_Cost_to_Complete),
	Sum({<Cost_Code={8,10,12}>}Prior_Forecast_Cost_to_Complete))))
)
*
(
	If(Cost_Code=8, Sum({<Cost_Code={8}>}POC)/100
	-
	Above(TOTAL Sum({<MonthYear,Year,YearMonth,Cost_Code={8}>}POC)/100, Num(Month)),
	If(Cost_Code=10, Sum({<Cost_Code={10}>}POC)/100
	-
	Above(TOTAL Sum({<MonthYear,Year,YearMonth,Cost_Code={10}>}POC)/100, Num(Month)),
	If(Cost_Code=12, Sum({<Cost_Code={12}>}POC)/100
	-
	Above(TOTAL Sum({<MonthYear,Year,YearMonth,Cost_Code={12}>}POC)/100, Num(Month)),
	Sum({<Cost_Code={8,10,12}>}POC)/300
	-
	Above(TOTAL Sum({<MonthYear,Year,YearMonth,Cost_Code={8,10,12}>}POC)/300, Num(Month)))))
)
)
), Project_Number, Year,MonthYear)

thanks

Bhavesh

[vvira1316]

It is because MTD Impact formula uses Above function. Once you select a particular month there is no other visible record in the table hence it is not able to do calculation.

Ideally you should use Master Calendar with AsOfMonth functionality. Also instead of having such a complicated calculation in the UI it should be calculated in the script.