cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

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. 

AccountEnd of MonthAmount
21010 · Accounts PayableThursday, January 31, 2019-460,516.69
21010 · Accounts PayableThursday, February 28, 2019359,506.42
21010 · Accounts PayableSunday, March 31, 2019-558,546.33
21010 · Accounts PayableTuesday, April 30, 2019715,404.94
21010 · Accounts PayableFriday, May 31, 2019192,266.02
21010 · Accounts PayableSunday, June 30, 201927,008.43
21010 · Accounts PayableWednesday, July 31, 2019-432,746.89
21010 · Accounts PayableSaturday, August 31, 2019-336,590.16
21010 · Accounts PayableMonday, September 30, 2019551,861.02
21010 · Accounts PayableThursday, October 31, 2019-693,064.65
21010 · Accounts PayableSaturday, November 30, 2019-929,912.33
21010 · Accounts PayableTuesday, December 31, 2019627,289.88
21010 · Accounts PayableFriday, January 31, 2020506,005.72
21010 · Accounts PayableSaturday, February 29, 2020-99,913.04
21010 · Accounts PayableTuesday, March 31, 2020-85,338.85
21010 · Accounts PayableThursday, April 30, 2020-12,684.61
21010 · Accounts PayableSunday, May 31, 2020209,643.36
21010 · Accounts PayableTuesday, June 30, 2020-607,534.64
21010 · Accounts PayableMonday, December 31, 2018-725,219.03
11010 · Cash-Chase CheckingThursday, January 31, 2019379,082.18
11010 · Cash-Chase CheckingThursday, February 28, 2019356,285.90
11010 · Cash-Chase CheckingSunday, March 31, 2019-648,773.44
11010 · Cash-Chase CheckingTuesday, April 30, 201948,104.83
11010 · Cash-Chase CheckingSunday, June 30, 2019-280,666.70
11010 · Cash-Chase CheckingWednesday, July 31, 2019547,017.34
11010 · Cash-Chase CheckingSaturday, August 31, 2019-217,955.31
11010 · Cash-Chase CheckingMonday, September 30, 2019-182,446.73
11010 · Cash-Chase CheckingSaturday, November 30, 2019-613,046.85
11010 · Cash-Chase CheckingTuesday, December 31, 2019-126,870.59
11010 · Cash-Chase CheckingFriday, January 31, 2020508,440.83
11010 · Cash-Chase CheckingSaturday, February 29, 2020-394,431.20
11010 · Cash-Chase CheckingTuesday, March 31, 2020-122,365.01
11010 · Cash-Chase CheckingThursday, April 30, 2020-375,733.21
11010 · Cash-Chase CheckingSunday, May 31, 2020753,068.04
11010 · Cash-Chase CheckingTuesday, June 30, 2020-452,400.43
11010 · Cash-Chase CheckingFriday, May 31, 2019272,087.62
11010 · Cash-Chase CheckingThursday, October 31, 2019970,930.54
11010 · Cash-Chase CheckingMonday, December 31, 2018-32,813.06
11040 · Accounts Receivable -  StorageThursday, January 31, 2019770,194.65
11040 · Accounts Receivable -  StorageThursday, February 28, 2019-529,464.30
11040 · Accounts Receivable -  StorageSunday, March 31, 2019730,106.49
11040 · Accounts Receivable -  StorageTuesday, April 30, 2019-680,012.99
11040 · Accounts Receivable -  StorageFriday, May 31, 201995,405.75
11040 · Accounts Receivable -  StorageSunday, June 30, 2019-191,929.20
11040 · Accounts Receivable -  StorageWednesday, July 31, 2019-294,148.33
11040 · Accounts Receivable -  StorageSaturday, August 31, 2019824,700.49
11040 · Accounts Receivable -  StorageMonday, September 30, 2019269,638.83
11040 · Accounts Receivable -  StorageThursday, October 31, 2019-470,359.77
11040 · Accounts Receivable -  StorageSaturday, November 30, 20191,024,356.34
11040 · Accounts Receivable -  StorageTuesday, December 31, 2019130.50
11040 · Accounts Receivable -  StorageFriday, January 31, 2020392,481.31
11040 · Accounts Receivable -  StorageSaturday, February 29, 2020227,028.55
11040 · Accounts Receivable -  StorageTuesday, March 31, 2020964,390.64
11040 · Accounts Receivable -  StorageThursday, April 30, 2020-6,752.01
11040 · Accounts Receivable -  StorageSunday, May 31, 2020-253,979.49
11040 · Accounts Receivable -  StorageTuesday, June 30, 2020840,380.74
11040 · 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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

8 REPLIES 8
Super User IV
Super User IV

@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.

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Memorable Member
Memorable Member

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

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

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

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

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. 

@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 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Community Support
Community Support

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors