DAX Closing and Opening Balances

[JTwohig]

I have a table that contains a daily snapshot of an amount. I can get the amount at the end of each month with this formula:

Closing Balance:=CALCULATE(Sum(FactTable[AmountToDate]),LASTDATE(FactTable[Date]))

I need the balance at the end of the prior month so I can subtract and get the change in the month. I tried the following formula:

Last Month Closing:=CALCULATE([Closing Balance],PREVIOUSMONTH(LASTDATE(FactTable[Date])))

This gives me the correct closing balance for the last month of the year but all other months are blank. The results show below. I have tried many different ways but always get the same thing. What am I missing?

		Values
CalendarYear	MonthName	Closing Balance	Last Month Closing
2016	March	59,791,699.31
	April	62,596,269.14
	May	51,852,607.79
	June	53,945,802.67
	July	53,729,133.16
	August	57,174,277.35
	September	56,780,543.07
	October	57,852,783.08
	November	55,973,021.78
	December	57,976,571.96
2016 Total		57,976,571.96	55,973,021.78
2017	January	55,479,645.44
	February	50,214,362.52
	March	44,361,759.05
	April	46,053,405.57
	May	48,841,553.83
	June	52,345,876.41
	July	56,329,590.12
	August	55,859,204.39
2017 Total		55,859,204.39	56,329,590.12
Grand Total		55,859,204.39	56,329,590.12

[Vvelarde · Accepted solution]

@JTwohig

Hi, if you always finished in the end of the month. this can help you

ClosingBalance-1month-Alt =
VAR EndofPrevMonth =
    PREVIOUSMONTH ( Table1[Date] )
RETURN
    CALCULATE (
        SUM ( Table1[Amount] ),
        FILTER ( ALL ( Table1 ), Table1[Date] = EndofPrevMonth )
    )

Also you can review this DAX Functions:

OPENINGBALANCEMONTH

CLOSINGBALANCEMONTH

Regards

Victor

Lima - Peru