Closing balances with missing months and summation over accounts

[AndreasH]

Hello!

I have searched all over for closing balance solutions, and I have found many but none that answers my problem.

To try and make a clear explanation I have put up an example in a screen shot.

To the left there is the fact table with values and balances for each account. I want to use the "Balance" column since I might not have a "starting value" in the value column. To the right there is 3 scenarios that I would like to be able to filter in Power BI. When I try this with DAX I cannot get the missing month to be populated with the last non blank value. Also I can't figure out if there is a way that the balances can be summed up if I don't filter on one account. In addition I have a date dimension that I can use to create the example output.

Anyone have an idea on how to solve this?

/Andrea

[Ashish_Mathur]

Hi @AndreasH,

You may download my solution from here.

Hope this helps.

[BILASolution]

Hi @AndreasH

To answer your question, follow the next steps...

0. "Balance" is your fact table.

1. I created a new table called "Account" with the DAX expression...

Account = DISTINCT(Balance[Account])

2. I created a new table called "Date" with the DAX expression...

Date = CALENDAR("01/01/2017";"31/12/2017") (The range depends of your data)

3. Then I created some new columns into Date Table...

Year = YEAR('Date'[Date]) 

Month = FORMAT('Date'[Date];"MMMM")

Month Number = MONTH('Date'[Date])

To sort the Month column --> On the ribbon: Modeling --> Sort by column (Choose Month Number)

4. After that, I created a new table called "Balance Total" with...

Balance Total = CROSSJOIN(Account;'Date')

5. I created the next relationship between tables...

6. I created a new column into "Balance Total" table called "Value" with ...

Value = IF(ISBLANK(LOOKUPVALUE(Balance[Value];Balance[Date];'Balance Total'[Date];Balance[Account];'Balance Total'[Account]));0;LOOKUPVALUE(Balance[Value];Balance[Date];'Balance Total'[Date];Balance[Account];'Balance Total'[Account])) 

7. Finally, I created a new measure called Balance into "Balance Total" table with...

Balance = TOTALYTD(SUM('Balance Total'[Value]);'Date'[Date])

Aditional:

This is the sample data I used..."Balance" table

And the final report is... (Hiding some useless columns)

Regards

BILASolution

[AndreasH]

Hi!

Thank you for the reply. It seem to work but it is a bit tricky to make this crossjoin when the amount of data is a bit larger than the example. I have tried with this DAX to add a measure to the "Balance" table. 

Measure = CALCULATE(SUM(Balance[Value]);FILTER(ALL(Date[Date].[Date]);Date[Date].[Date]<=MAX(Date[Date].[Date])))

It works fine except for one thing. It can not handle the starting balance if it differs from the first Balance[Value]. See example below. I feel that it should be an easy thing to add the first occurance of the balance to the calculation but I can't get it to work. 

Any ideas?

[Ashish_Mathur]

Hi @AndreasH,

Whom are you replying to?  Have you tried out my solution?

[AndreasH]

I didn't get that to work with the total balance of all accounts. Also when the first transaction is from previous year and when the firsta transaction and balance is different. See my example above. What I would need is a Init balance value + the calculation above. I think that would do it.

[BILASolution]

Hi @AndreasH

Replace my previous measure "Balance" by this...

Balance = var anoactual = FIRSTNONBLANK('Date'[Year];1) var mesactual = FIRSTNONBLANK('Date'[Month];1)
          var numeromesactual = LOOKUPVALUE('Date'[Month Number];'Date'[Month];mesactual)
		  var balances = CALCULATE([Total Value];ALL('Date');'Date'[Year] = anoactual ; 'Date'[Month Number] <= numeromesactual)
          var totalpastyears = CALCULATE([Total Value];ALL('Date');'Date'[Year] < anoactual)

return

IF(numeromesactual = 1 ; totalpastyears + [Total Value];totalpastyears+balances) 

 and the second case...

Balance by Date = var actualdate = FIRSTNONBLANK('Date'[Date];1) return 
IF(ISBLANK([Total Value]);BLANK();                
          CALCULATE([Total Value];ALL('Date'[Date]);'Date'[Date] <= actualdate)
)

This is the result...

[BILASolution]

Hi @AndreasH

Follow until "step 3" in my previous answer...then

4. I created the next measures

Total Value = SUM(Balance[Value])

Balance = var anoactual = FIRSTNONBLANK('Date'[Year];1) var mesactual = FIRSTNONBLANK('Date'[Month];1)
          var numeromesactual = LOOKUPVALUE('Date'[Month Number];'Date'[Month];mesactual)

return

CALCULATE([Total Value];ALL('Date');'Date'[Year] = anoactual ; 'Date'[Month Number] <= numeromesactual) 

5. The final result is...

NOTE: In Month field, set to "Show Items with no Data"

I hope this helps

Regards

BILASolution

[BILASolution]

@AndreasH

(Thinking...)