Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tcasekpi
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
v-eachen-msft
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
v-eachen-msft
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.
andre
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, when applied this results in a column in my table that is equal to the amount already listed there. 

amitchandak
Super User
Super User

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

 

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

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.