Looking for assistance with a running total balance for multiple accounting periods using data from

[MLBLom]

Fairly new to Power BI.  I need to be able to take a ‘projected’ revenue amount  ‘TotalEstimatedRev’[TotalEstimatedRev] from one table as a starting amount for year 2023 - amount is  $73,377,835.88. 

For accounting periods 1 – 12 need to have a running total balance with starting amount being ‘TotalEstimatedRev’[TotalEstimatedRev]and add the difference between the (Revenue Booked to Date minus Expended to Date which come from two other tables.

 I have created a measure for the difference called ‘SB_PBI_VS_SB_ADM_REV_TB’[6RevExpDiffNew]. 

The ‘SB_PBI_VS_SB_ADM_REV_TB’[6RevExpDiffNew] amount for a particular accounting period needs to be added to the running total balance amount from the month before.  

6REVEXPDIFFNEW =

('SB_PBI_VW_SB_ADM_REV_TB'[TOTALREVwoNegatives]) - ('SB_PBI_VW_COMBINED_BUDGET_EXPENSE_DATA'[2TotalExpense])

More detail behind the measure 6REVEXPDIFFNEW measure:

TOTALREVwonegatives =

CALCULATE(SUM('SB_PBI_VW_SB_ADM_REV_TB'[TOTAL_REV]), FILTER(SB_PBI_VW_SB_ADM_REV_TB, 'SB_PBI_VW_SB_ADM_REV_TB'[TOTAL_REV] >= 0))

2TotalExpense = ('SB_PBI_VW_COMBINED_BUDGET_EXPENSE_DATA'[MTD] + 'SB_PBI_VW_COMBINED_BUDGET_EXPENSE_DATA'[RUNNINGENCUMBRANCE])

RunningEncumbrance =

CALCULATE([3 New Encumbrance Amount],

                   Window(

                       1, ABS,

                   0, REL,

                       SUMMARIZE(ALLSELECTED('SB_PBI_VW_COMBINED_BUDGET_EXPENSE_DATA'), 'SB_PBI_VW_COMBINED_BUDGET_EXPENSE_DATA'[ACCOUNTING_PERIOD]),

                       ORDERBY('SB_PBI_VW_COMBINED_BUDGET_EXPENSE_DATA'[ACCOUNTING_PERIOD])))

3 New Encumbrance Amount = CALCULATE(SUM(SB_PBI_VW_COMBINED_BUDGET_EXPENSE_DATA[TOTAL_EXPENSED_AMT]), FILTER(SB_PBI_VW_COMBINED_BUDGET_EXPENSE_DATA, SB_PBI_VW_COMBINED_BUDGET_EXPENSE_DATA[LEDGER]="ORG_EN"))

MTD =

VAR MAXACCTPERIOD2='SB_PBI_VW_COMBINED_BUDGET_EXPENSE_DATA'[MAXACCTPERIOD]

RETURN

CALCULATE(SUM(SB_PBI_VW_COMBINED_BUDGET_EXPENSE_DATA[TOTAL_EXPENSED_AMT]), FILTER(SB_PBI_VW_COMBINED_BUDGET_EXPENSE_DATA, SB_PBI_VW_COMBINED_BUDGET_EXPENSE_DATA[LEDGER]="ORG_EX"), FILTER('SB_PBI_VW_COMBINED_BUDGET_EXPENSE_DATA', 'SB_PBI_VW_COMBINED_BUDGET_EXPENSE_DATA'[ACCOUNTING_PERIOD]=MAXACCTPERIOD2))

Outputs below:

However, my Running Balance/Running Total is not totaling properly. 

Below is my current measure for Running balance:

Running Balance5 = 'SB_PBI_VW_SB_ADM_REV_TB'[6REVEXPDIFFNEW] + SELECTEDVALUE('TotalRevenueEstimates'[TotalEstimatedRev])

Accounting period 1 is correct but periods 2 through 12 are not.

For example   start with the projected amount $73,377.835.88 + $696,521.08 = Acct period 1 $74,074,356.96  (CORRECT)

Acct period 2 – should take the total from acct period 1 $74,074,356.96 +  ($92,862.21) .  This should total $73,981,494.75   but it is calculating Acct period 2 as $73,745,013.16 (incorrect).    All additional periods are incorrect as well.  

Any guidance would be appreciated.  Thanks!

[v-tangjie-msft · Accepted solution]

Hi @MLBLom ,

According to your description, here are my steps you can follow as a solution.

(1) We can create a measure. 

Measure = 
 var _a=CALCULATE([Running Total],FILTER(ALL('Table'),'Table'[Accounting Period]=1))
 var _b=_a+[Difference]
 return 
 IF(MAX('Table'[Accounting Period])=1,[Running Total],_b)

(2) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.