Finance Report - Balance sheet - show most recent value

[carolinastalo]

Spoiler

Hello

Hello 

I have a data set with balance sheet values.

My table is like this:

Account	Date	Balance End of month
1	31/01/2019	100
2	31/01/2019	150
3	31/01/2019	60
1	28/02/2019	150
3	28/02/2019	120
2	31/03/2019	500
1	30/04/2019	200
4	30/06/2019	1000

So I have the balance at the end of month for the accounts. The problem is that not all accounts have balance in all months (for example, for some accounts that did not have any transactions in a specified month). 

I need to show something like this, if the time slicer is in june/2019 for example;

Account	Balance
1	200
2	500
3	120
4	1000

I need to show the balance for all accounts - for a month in a time slicer.

However, if the account does not have balance in the choosen month, the data does not appear (and the balance sheet report is wrong). Tried many approaches from this forum, but none solved my specific situation

Any help is appreciated! Thanks a lot!

[Anonymous · Accepted solution]

Total Balance =
var __lastVisibleDate = max( 'Calendar'[Date] )
VAR __accountsWithDates =
	calculatetable (
		addcolumns (
		    summarize (
		        Balances,
		        Accounts[AccountId]
		    ),
		    "LastDate",
		    	calculate (
		    		max( Balances[Date] )
		    	)
		),
	    'Calendar'[Date] <= __lastVisibleDate
	)
VAR __accountsWithDatesWithLineage =
    TREATAS (
        __accountsWithDates,
        'Account'[AccountId],
        'Calendar'[Date]
    )
VAR __result =
    CALCULATE (
        sum ( Balances[Balance] ),
        __accountsWithDatesWithLineage
    )
RETURN
	__result

This version might be faster than the one I posted before (it returns the same results, of course, but does not use iteration). Bear in mind that you should have a Calendar---a proper Date table---connected to the field Date in Balances. Also, you should only slice and dice by dimensions and the fact table's columns must be hidden. Only measures can be visible in the fact table.

Best

D

[carolinastalo]

Man, thanks A LOT! It was exactly that. I need to study more about creating variables. I did only basic /intermediate courses about DAX and get a little lost some times.

[Anonymous]

// Say you have a Calendar connected to Date
// in Balances (or it could be disconnected as well).
// You also have an Accounts dimension
// that stores the unique Accounts. And you
// have a fact table that stores the Balances
// at the end of the months. As you say, some
// accounts will not have entries for some
// months. Accounts is connected to Balances
// via AccountID (1:*). Slicing can only be
// done via dimensions. All columns in the
// fact must be hidden.

// balance when only 1 account is visible
[_Balance41Account] = // hidden measure
var __oneAccountVisible = hasonevalue( Accounts[AccountID] )
var __maxDate = max( Calendar[Date] )
var __maxDateOfBalance =
	calculate(
		MAX( Balances[Date] ),
		Balances[Date] <= __maxDate,
		ALL( 'Calendar' )
	)
var __balance =
	calculate(
		values( Balances[Balance] ),
		Balances[Date] = __maxDateOfBalance,
		ALL( 'Calendar' )
	)
return
	if( __oneAccountVisible, __balance )
	
// Final measure - visible
[Total Balance] = // works OK for any selection of accounts
	sumx(
		values( Accounts[AccountId] ),
		[_Balance41Account]
	)

There is a version that does not use iteration - check my post after this one. The second version should be faster on big tables.

Best

D

[az38]

Hi @carolinastalo 

first, create a calendar table with last month days

CalendarTable = FILTER(ADDCOLUMNS(calendar(date(2019,1,1),date(2019,12,31)),"EndMonth",IF(EOMONTH([Date],0) = [Date],TRUE(),FALSE())),[EndMonth]=true())

then add Date field from calendar table to slicer and create a measure in your source table

Measure = 
var _slicerDate = if(ISFILTERED(CalendarTable[Date]),CALCULATE(MAX(CalendarTable[Date])),today())
return
CALCULATE(MAX('Table'[Balance End of month]),'Table'[Date]<=_slicerDate)

[carolinastalo]

Hello, thanks for the reply.

Forgot to explain something:

In the report I have titles, and need to return subtotals, like this:

CURRENT LIABILITIES        (Sum of Values)

Account 1             (Value)

Account 2            (Value)

NONCURRENT LIABILITIES     (Sum of Values)

Account 3           (Value)

I tried this, however if I use MAX, it will also show the MAX value on the subtotals, instead of showing the sum. I need to show not the MAX value of Balance End of month, but just the value. How do I do this?

[Greg_Deckler]

So at that point, that sounds like a measures total problem. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Anonymous]

To get sums you have to iterate the accounts and for each you have to find the balance on the day you want.

And here's the best place to find stuff like this https://www.daxpatterns.com/patterns/

Best
D

[az38]

Sorry, @carolinastalo 

copy-paste 🙂

Measure = 
var _slicerDate = if(ISFILTERED(CalendarTable[Date]),CALCULATE(MAX(CalendarTable[Date])),today())
var _lastDate = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Account]),'Table'[Date]<=_slicerDate)
return
CALCULATE(MAX('Table'[Balance End of month]),'Table'[Date]=_lastDate)

[Greg_Deckler]

So like the attached?