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

[JTwohig]

I thought I would post this in case someone else is trying to do the same thing I was. 

There were two solutions suggested here that both worked when I looked at the whole table. However, when I tried to filter the results by customer or some other dimension the last month closing balance remained the last month balance for everything. It was not filtered.

What worked was to remove only the filter from the date rather than the whole table. If I understand the terminology that means it keeps the context for everything else but not for the dates.

instead of using FILTER(All(FactTable),

I used Filter(All(FactTable[CalendarYear],FactTable[MonthName],FactTable[Date]),

That way I got the correct balances whether I was looking at the whole table or only looking at certain dimensions.

[nickchobotar]

@JTwohig

Before this post is closed. What happens if you use FILTER(ALLSELECTED(FactTable)) instead of

Filter(All(FactTable[CalendarYear],FactTable[MonthName],FactTable[Date]),?

Also, please consider adding a date dimension to your model.

[JTwohig]

That worked as well. I like it much better because it is simpler. Thanks.

I do have a date dimension, along with several others. My model uses data from two fact tables so the only way to get the correct data from each fact table is to use separate dimiensions. 

[nickchobotar]

Hi @JTwohig

Great! I am glad to hear the ALLSELECTED() worked for you.

If I understood you correctly you are using two separate dimensions that play the same role to filter your two fact tables. If so then there is a room to optimize your model  so you can use only one Date dimension. If you are interested please post a picture of your model diagram.

N-

[JTwohig]

No, they are different dimensions. I have one date dimension plus ones for customer, project and business unit.

[nickchobotar]

@JTwohig

I see. In that case, how come you are not using your Date dimension for this time calcuation but use your Fact table ?

[JTwohig]

I do use the date dimension for the calculations but when I posted the example I was troubleshooting and removed all other tables to try to simplify as much as possible. I wanted to be sure the issue wasn't coming from an incorrect join. I also simplified the calculation as well. I am not really just getting an opening balance. I am doing calculations with several opening and closing balances. 

When troubleshooting I like to get down to the simplest possible situation where I get the error to pinpoint exactly what is going wrong.

Everything eventually worked well. I deployed the model to SSAS, created my Power BI report, and published it to a web part in SharePoint. It refreshes from a SQL Server database nightly.

[nickchobotar]

@JTwohig

Excellent! It is clear now. Please mark this post as solved.

N -

[nickchobotar]

@JTwohig

You were on the right track with your Last Month Closing formula. You just needed to have your LASTDATE() in front becuase PREVIOUSMONTH() gives us a SUM of all the month and with LASTDATE() you filter down to the last date of the month. Using LASTDATE in a variable here is an overkill I think.

Closing Blance Prev Month = 
CALCULATE(
	[Sales Amount],
	LASTDATE(PREVIOUSMONTH('Date'[Date]))
)

Nick  -

[JTwohig]

Nick

That didn't work for me. I changed it to Sum([Sales Amount]) because it said that a single value couldn't be determined. However, even then, it gave me blanks for all months.

I don't understand why but, even when I tried in several different ways, there seems to be something about using the variable that makes it work.

[nickchobotar]

@JTwohig

The most important thing that you have a working solution.

I see that you are referencing only one table (Table1) both for dates and facts which makes me assume that you are not using a separate Date dimension, that's why my solution did not work for you. My recommendation would be to include a Date dimension since you are doing time intelligence stuff.

N-

[JTwohig]

You are correct. I do have a separate date dimension but I put it all in one table while troubleshooting to try and make it as simple as possible.

Thanks

[fhill · Accepted solution]

FINALLY!!!!    I got it....  This link helped!

https://community.powerbi.com/t5/Desktop/Help-using-Earlier-in-New-Measure/td-p/55799

EndofPriorMonth = CALCULATE(SUM(Table1[Balance]), FILTER(ALL(Table1), SUMX( FILTER( Table1, EARLIER(Table1[Date]) = LASTDATE(PREVIOUSMONTH(Table1[Date])) ), Table1[Balance])))

   ** What this does.. .Sum Blance,

                 Look at ALL Rows, (Filter ALL)

                 SUMX (Sums for each row of....)

                 Filter again (not sure why)

                 Compare 'previous row' (EARLIER) with Last Date of Pervious Month

                 When found, return Balance.

EndOfMonth = CALCULATE(SUM(Table1[Balance]), ENDOFMONTH(Table1[Date]))

Change = [EndOfMonth] - [EndofPriorMonth]

[kristenk14]

Hi @fhill,

I am sitting with a similar problem, and I tried your solution. I want to obtain the opening balances for all my individual accounts for every month.  I tried this measure 

opening_balance = CALCULATE([flow_value], FILTER(ALL(investor_flows), SUMX( FILTER( investor_flows, EARLIER(investor_flows[date]) = LASTDATE(PREVIOUSMONTH(investor_flows[date])) ), investor_flows[value])))

I get answers but It is not correct please see attachment 

ANy help would really be appreciated.

Thank you

[JTwohig]

That also works. 

Thanks

[fhill]

Power BI / DAX has great features for this!  ** August below shows 7000 twice b/c I don't have an EOD yet for AUG in my table, it will take the highest date that month instead. **

StartofMonth = CALCULATE(SUM(Table1[Balance]), STARTOFMONTH(Table1[Date]))

EndOfMonth = CALCULATE(SUM(Table1[Balance]), ENDOFMONTH(Table1[Date]))

Change = [EndOfMonth] - [StartofMonth]

[JTwohig]

Thanks for the suggestion but it's not quite there - one day off.

I need to know the change from one month to the next. Your example shows that the change was 5,500 in July but it was really 5,600. It increased by 100 (From 400 to 500) on July 1. 

I like the ENDOFMONTH function better than what I had used.

I can't help but think I am missing something simple in not being able to get the balance at the end of the previous month.

[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

[JTwohig]

Thanks Victor

I did have to make one change. I added the LASTDATE:

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

I have been trying to get this to work for a while. I appreciate the help.

[fhill]

I've been trying to throw a 'DateAdd(.....,-1,Month) in there somewhere, but it's not liking the months chaning...  I'll have to try more in the morning.

FOrrest