Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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.
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.
@tcasekpi , how to use the closing balance, refer
https://www.facebook.com/watch/?v=343509629992272
Make sure you use date calendar
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
103 | |
103 | |
88 | |
66 |