SUMX not working (Opening & Closing Balance)

[mhicoayala]

Hi,

I am trying to perform a SUMX with the measure below, and putting 'Month' filter in a Table so I can have the sum of all instances of 'Closing Balance', but it just doesn't seem to work:

Closing Balance = 

VAR _startOfTheMonth = STARTOFMONTH('Calendar'[Date])
VAR _endOfTheMonth = ENDOFMONTH('Calendar'[Date])

VAR _startBeginningPeriod = SUMX(Manpower,
                                IF(Manpower[Date Assigned To Current Project] <= _endOfTheMonth, 1, 0)
)

VAR _startEndPeriod = SUMX(Manpower,
                            IF(Manpower[Last Reporting Date / Resignation] <= _endOfTheMonth && NOT(ISBLANK(Manpower[Last Reporting Date / Resignation])), 1, 0)
)

RETURN
_startBeginningPeriod - _startEndPeriod

 
This is how it looks like:

 I'm really at an impasse here and I would appreciate it if you could answer this.

Thanks!

[amitchandak · Accepted solution]

@mhicoayala , Change return like this

Sumx(values(calendar[Month year]), _startBeginningPeriod - _startEndPeriod)

or create one more measure

Sumx(values(calendar[Month year]),[Closing Balance])

[mhicoayala]

Oh my god thank you so much!!!!