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. AccountEnd of MonthAmount21010 · Accounts PayableThursday, January 31, 2019-460,516.6921010 · Accounts PayableThursday, February 28, 2019359,506.4221010 · Accounts PayableSunday, March 31, 2019-558,546.3321010 · Accounts PayableTuesday, April 30, 2019715,404.9421010 · Accounts PayableFriday, May 31, 2019192,266.0221010 · Accounts PayableSunday, June 30, 201927,008.4321010 · Accounts PayableWednesday, July 31, 2019-432,746.8921010 · Accounts PayableSaturday, August 31, 2019-336,590.1621010 · Accounts PayableMonday, September 30, 2019551,861.0221010 · Accounts PayableThursday, October 31, 2019-693,064.6521010 · Accounts PayableSaturday, November 30, 2019-929,912.3321010 · Accounts PayableTuesday, December 31, 2019627,289.8821010 · Accounts PayableFriday, January 31, 2020506,005.7221010 · Accounts PayableSaturday, February 29, 2020-99,913.0421010 · Accounts PayableTuesday, March 31, 2020-85,338.8521010 · Accounts PayableThursday, April 30, 2020-12,684.6121010 · Accounts PayableSunday, May 31, 2020209,643.3621010 · Accounts PayableTuesday, June 30, 2020-607,534.6421010 · Accounts PayableMonday, December 31, 2018-725,219.0311010 · Cash-Chase CheckingThursday, January 31, 2019379,082.1811010 · Cash-Chase CheckingThursday, February 28, 2019356,285.9011010 · Cash-Chase CheckingSunday, March 31, 2019-648,773.4411010 · Cash-Chase CheckingTuesday, April 30, 201948,104.8311010 · Cash-Chase CheckingSunday, June 30, 2019-280,666.7011010 · Cash-Chase CheckingWednesday, July 31, 2019547,017.3411010 · Cash-Chase CheckingSaturday, August 31, 2019-217,955.3111010 · Cash-Chase CheckingMonday, September 30, 2019-182,446.7311010 · Cash-Chase CheckingSaturday, November 30, 2019-613,046.8511010 · Cash-Chase CheckingTuesday, December 31, 2019-126,870.5911010 · Cash-Chase CheckingFriday, January 31, 2020508,440.8311010 · Cash-Chase CheckingSaturday, February 29, 2020-394,431.2011010 · Cash-Chase CheckingTuesday, March 31, 2020-122,365.0111010 · Cash-Chase CheckingThursday, April 30, 2020-375,733.2111010 · Cash-Chase CheckingSunday, May 31, 2020753,068.0411010 · Cash-Chase CheckingTuesday, June 30, 2020-452,400.4311010 · Cash-Chase CheckingFriday, May 31, 2019272,087.6211010 · Cash-Chase CheckingThursday, October 31, 2019970,930.5411010 · Cash-Chase CheckingMonday, December 31, 2018-32,813.0611040 · Accounts Receivable - StorageThursday, January 31, 2019770,194.6511040 · Accounts Receivable - StorageThursday, February 28, 2019-529,464.3011040 · Accounts Receivable - StorageSunday, March 31, 2019730,106.4911040 · Accounts Receivable - StorageTuesday, April 30, 2019-680,012.9911040 · Accounts Receivable - StorageFriday, May 31, 201995,405.7511040 · Accounts Receivable - StorageSunday, June 30, 2019-191,929.2011040 · Accounts Receivable - StorageWednesday, July 31, 2019-294,148.3311040 · Accounts Receivable - StorageSaturday, August 31, 2019824,700.4911040 · Accounts Receivable - StorageMonday, September 30, 2019269,638.8311040 · Accounts Receivable - StorageThursday, October 31, 2019-470,359.7711040 · Accounts Receivable - StorageSaturday, November 30, 20191,024,356.3411040 · Accounts Receivable - StorageTuesday, December 31, 2019130.5011040 · Accounts Receivable - StorageFriday, January 31, 2020392,481.3111040 · Accounts Receivable - StorageSaturday, February 29, 2020227,028.5511040 · Accounts Receivable - StorageTuesday, March 31, 2020964,390.6411040 · Accounts Receivable - StorageThursday, April 30, 2020-6,752.0111040 · Accounts Receivable - StorageSunday, May 31, 2020-253,979.4911040 · Accounts Receivable - StorageTuesday, June 30, 2020840,380.7411040 · Accounts Receivable - StorageMonday, December 31, 20182,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.

Forum Discussion

tcasekpi

New Member

6 years ago

Solved

Rolling Balance Sheet Balance

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.

Need Help

v-eachen-msft

6 years ago

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.

test.pbix71 KB

9 Replies

amitchandak
Super User
6 years ago
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

Appreciate your Kudos.
- tcasekpi
  New Member
  6 years ago
  Thank you for the reply. This causes a circular dependency in my table. I do not know how to resolve.
  - andre
    Memorable Member
    6 years ago
    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
andre
Memorable Member
6 years ago
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
  New Member
  6 years ago
  Thank you for the reply, when applied this results in a column in my table that is equal to the amount already listed there.

v-eachen-msft

Community Support

6 years ago

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.

test.pbix71 KB

Jidnyasa2904
Helper I
1 year ago
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.

Forum Discussion

Rolling Balance Sheet Balance

9 Replies

Related content

Inventory and Stock balance calculation

Remaining Balance Depends on Prior Month

Rolling balance

Latest Closing Balance

ENDING BALANCE AS BEGINNING BALANCE NEXT DAY