Rolling Balance Sheet Balance

[tcasekpi]

Hello, 

I am new to Power BI and I am having trouble adding a cumulative column. 

I have a table that shows the actitvity by account by month that I want to use to show the ending balance every month. 

Account	End of Month	Amount
21010 · Accounts Payable	Thursday, January 31, 2019	-460,516.69
21010 · Accounts Payable	Thursday, February 28, 2019	359,506.42
21010 · Accounts Payable	Sunday, March 31, 2019	-558,546.33
21010 · Accounts Payable	Tuesday, April 30, 2019	715,404.94
21010 · Accounts Payable	Friday, May 31, 2019	192,266.02
21010 · Accounts Payable	Sunday, June 30, 2019	27,008.43
21010 · Accounts Payable	Wednesday, July 31, 2019	-432,746.89
21010 · Accounts Payable	Saturday, August 31, 2019	-336,590.16
21010 · Accounts Payable	Monday, September 30, 2019	551,861.02
21010 · Accounts Payable	Thursday, October 31, 2019	-693,064.65
21010 · Accounts Payable	Saturday, November 30, 2019	-929,912.33
21010 · Accounts Payable	Tuesday, December 31, 2019	627,289.88
21010 · Accounts Payable	Friday, January 31, 2020	506,005.72
21010 · Accounts Payable	Saturday, February 29, 2020	-99,913.04
21010 · Accounts Payable	Tuesday, March 31, 2020	-85,338.85
21010 · Accounts Payable	Thursday, April 30, 2020	-12,684.61
21010 · Accounts Payable	Sunday, May 31, 2020	209,643.36
21010 · Accounts Payable	Tuesday, June 30, 2020	-607,534.64
21010 · Accounts Payable	Monday, December 31, 2018	-725,219.03
11010 · Cash-Chase Checking	Thursday, January 31, 2019	379,082.18
11010 · Cash-Chase Checking	Thursday, February 28, 2019	356,285.90
11010 · Cash-Chase Checking	Sunday, March 31, 2019	-648,773.44
11010 · Cash-Chase Checking	Tuesday, April 30, 2019	48,104.83
11010 · Cash-Chase Checking	Sunday, June 30, 2019	-280,666.70
11010 · Cash-Chase Checking	Wednesday, July 31, 2019	547,017.34
11010 · Cash-Chase Checking	Saturday, August 31, 2019	-217,955.31
11010 · Cash-Chase Checking	Monday, September 30, 2019	-182,446.73
11010 · Cash-Chase Checking	Saturday, November 30, 2019	-613,046.85
11010 · Cash-Chase Checking	Tuesday, December 31, 2019	-126,870.59
11010 · Cash-Chase Checking	Friday, January 31, 2020	508,440.83
11010 · Cash-Chase Checking	Saturday, February 29, 2020	-394,431.20
11010 · Cash-Chase Checking	Tuesday, March 31, 2020	-122,365.01
11010 · Cash-Chase Checking	Thursday, April 30, 2020	-375,733.21
11010 · Cash-Chase Checking	Sunday, May 31, 2020	753,068.04
11010 · Cash-Chase Checking	Tuesday, June 30, 2020	-452,400.43
11010 · Cash-Chase Checking	Friday, May 31, 2019	272,087.62
11010 · Cash-Chase Checking	Thursday, October 31, 2019	970,930.54
11010 · Cash-Chase Checking	Monday, December 31, 2018	-32,813.06
11040 · Accounts Receivable -  Storage	Thursday, January 31, 2019	770,194.65
11040 · Accounts Receivable -  Storage	Thursday, February 28, 2019	-529,464.30
11040 · Accounts Receivable -  Storage	Sunday, March 31, 2019	730,106.49
11040 · Accounts Receivable -  Storage	Tuesday, April 30, 2019	-680,012.99
11040 · Accounts Receivable -  Storage	Friday, May 31, 2019	95,405.75
11040 · Accounts Receivable -  Storage	Sunday, June 30, 2019	-191,929.20
11040 · Accounts Receivable -  Storage	Wednesday, July 31, 2019	-294,148.33
11040 · Accounts Receivable -  Storage	Saturday, August 31, 2019	824,700.49
11040 · Accounts Receivable -  Storage	Monday, September 30, 2019	269,638.83
11040 · Accounts Receivable -  Storage	Thursday, October 31, 2019	-470,359.77
11040 · Accounts Receivable -  Storage	Saturday, November 30, 2019	1,024,356.34
11040 · Accounts Receivable -  Storage	Tuesday, December 31, 2019	130.50
11040 · Accounts Receivable -  Storage	Friday, January 31, 2020	392,481.31
11040 · Accounts Receivable -  Storage	Saturday, February 29, 2020	227,028.55
11040 · Accounts Receivable -  Storage	Tuesday, March 31, 2020	964,390.64
11040 · Accounts Receivable -  Storage	Thursday, April 30, 2020	-6,752.01
11040 · Accounts Receivable -  Storage	Sunday, May 31, 2020	-253,979.49
11040 · Accounts Receivable -  Storage	Tuesday, June 30, 2020	840,380.74
11040 · Accounts Receivable -  Storage	Monday, December 31, 2018	2,061,661.96

In my visual, I can select a date and get the right balance for that period. But I need to show the balance for cumulative balance for all months, not just the month selected. 

[Jidnyasa2904]

Following the similar topic, I want the balance of the end date of any selected date via slicers. I have multiple slicers like Month, Week, WTD/YTD/FullYear. However, I have disabled the WTD/YTD/FullYear slicer. 

Balance for the selected year =
VAR lastdate= CALCULATE(
    MAX('Date'[Date]),
    FILTER('Date ', 'Date '[Year] = SELECTEDVALUE('Max Year'[Year]))
)

VAR Result = CALCULATE(
    SUM('BalanceData'[Cost]),
    'Date'[Date] <= lastdate,  -- Use the adjusted enddate (365 days ago)
    REMOVEFILTERS('WTD/MTD/YTD/FullYear'),
    REMOVEFILTERS('Date')
)
RETURN
IF(Result = 0, Blank(), Result)

I only have to consider the last date of the selected slicers and calculate the value from the beginning of the dataset.  The above formula gives me that, but I want an average of 364 days from the end date. For that, I want the balance of all 364 days. 

[v-eachen-msft · Accepted solution]

Hi @tcasekpi ,

You could create a date table as a date filter. Then refer to the following measure:

Measure =
VAR a =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR b =
    EOMONTH ( a, -12 )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account] ),
            'Table'[End of Month] <= MAX ( 'Table'[End of Month] )
                && SELECTEDVALUE ( 'Table'[End of Month] ) <= a
                && SELECTEDVALUE ( 'Table'[End of Month] ) >= b
        )
    )

Here is my test file for your reference.

[andre]

Well, if you are dealing with balancesheet logic, then you cannot use YTD and QTD functions or as balancesheet amounts are not additive, you should be using CLOSINGBALANCEMONTH(), CLOSINGBALANCEQUARTER() and CLOSINGBALANCEYEAR() functions for MTD, QTD and YTD.

CLOSINGBALANCEYEAR also has a parameter to deal with fiscal year not being the same as calendar year

example End Of Month Balance = CLOSINGBALANCEMONTH(sum(gl[amount]), calendar[dates])

 or something like that

[tcasekpi]

Thank you for the reply, when applied this results in a column in my table that is equal to the amount already listed there. 

[amitchandak]

@tcasekpi ,

Please refer to these example with Date table

Monthly cummlative

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

Qtr

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Year

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

overall

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[tcasekpi]

Thank you for the reply. This causes a circular dependency in my table. I do not know how to resolve. 

[andre]

are you creating it as a column or a measure?  you should be doing it as a measure, also make sure you have a valid date table in your model

[tcasekpi]

I created it as a measure and use a dates table. What I am ultimately trying to do is take a transaction table and convert it to show 12 month rolling balance sheet balances based on a date slicer. 

I see a number of examples that work for revenue accounts but none for balance sheet cumulative totals. 

[amitchandak]

@tcasekpi , how to use the closing balance, refer 

https://www.facebook.com/watch/?v=343509629992272

https://community.powerbi.com/t5/Quick-Measures-Gallery/Allocating-Targets-in-Power-BI-Why-You-misse...

Make sure you use date calendar