Measures for balance sheet items

DAX DAX Calculations
1

hi,

i have created a table of a long list of measures (dax calculations) for financial calculations. it has no relationship to any other tables.

as income statement / profit & loss items are presented with cummalative/running totals, the measures i created for profit & loss items look like this:

60000-Supplies = 
    CALCULATE(
        SUM('finance'[Amount]),
            'chart-of-acct'[Account_Group_Code]=60000)

since balance sheet items are supposed to presented at a particular point in time (i.e. not cummalative), i’m not sure how the dax calculations are supposed to be for them. also, how do i present it in reports for each month and each year. i’d appreciate any help for these.

tks & krgds, -nik

2

Hi @nikahafiz,

You can use LASTNONBLANK function to get your required results. Please see video from Sam video.

In case of any confusion, please let me know.

3

Hi @nikahafiz, we’ve noticed that no response has been received from you since 20st of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

4

many tks for yr suggestion, @hafizsultan.

for balance sheet items that only involve month-end total, i have managed to come out with following dax (in this case, it’s for cash, a typical example of current assets, that comes/is filtered under account_group_code=10000):

10000-cash.month-end =
CALCULATE(
SUM(‘fin’[Amount]),
‘chart.of.acct’[Account_Group_Code]=10000,
ENDOFMONTH(‘fin’[Date])
)

for profit & loss item, i have created 2 types of totals i.e. month-end and cummulative/year-to-date as follows (in this case, it’s for supplies expense that comes/is filtered under account_group_code=60000):

a. month-end total

60000-supplies.month-end =
CALCULATE (
SUM ( ‘fin’[Amount] ),
‘chart.of.acct’[Account_Group_Code] = 60000,
ENDOFMONTH ( ‘fin’[Date] )
)

b. cummulative/year-to-date

60000-supplies.cummulative =
IF (
COUNTROWS ( ‘fin’ ),
TOTALYTD ( [60000-supplies], ‘cldr’[Date] )
)

the preceding dax for ‘60000-supplies’ will be as follows:

60000-supplies=
CALCULATE (
SUM ( ‘fin’[Amount] ),
‘chart.of.acct’[Account_Group_Code] = 60000
)

many tks again & kind regards, - nik