Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JTwohig
Helper I
Helper I

DAX Closing and Opening Balances

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 
CalendarYearMonthNameClosing BalanceLast Month Closing
2016March59,791,699.31 
 April62,596,269.14 
 May51,852,607.79 
 June53,945,802.67 
 July53,729,133.16 
 August57,174,277.35 
 September56,780,543.07 
 October57,852,783.08 
 November55,973,021.78 
 December57,976,571.96 
2016 Total 57,976,571.9655,973,021.78
2017January55,479,645.44 
 February50,214,362.52 
 March44,361,759.05 
 April46,053,405.57 
 May48,841,553.83 
 June52,345,876.41 
 July56,329,590.12 
 August55,859,204.39 
2017 Total 55,859,204.3956,329,590.12
Grand Total 55,859,204.39

56,329,590.12

2 ACCEPTED SOLUTIONS
Vvelarde
Community Champion
Community Champion

@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




Lima - Peru

View solution in original post

fhill
Resident Rockstar
Resident Rockstar

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]

 

Capture.PNG

 

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




View solution in original post

20 REPLIES 20
JTwohig
Helper I
Helper I

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.

@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.

 

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. 

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-

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

@JTwohig

 

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

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.

@JTwohig

 

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

 

N -

nickchobotar
Skilled Sharer
Skilled Sharer

@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  -

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.

 

@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-

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
Resident Rockstar
Resident Rockstar

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]

 

Capture.PNG

 

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




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 Capture.PNG
 
ANy help would really be appreciated.
Thank you

That also works. 

 

Thanks

fhill
Resident Rockstar
Resident Rockstar

 

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]

Capture.PNG




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




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
Community Champion
Community Champion

@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




Lima - Peru

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
Resident Rockstar
Resident Rockstar

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.