Total not working correctly

[Antmkjr]

https://drive.google.com/file/d/1SztSty7XnnX9Z4TBsVS25nDWON381Fuo/view?usp=sharing

In the above file,

1) why is total not displayed for funding?

The formula for funding is :

Funding =

IF([Balance Current Month]<0,[Balance Current Month]*-1)

The expected Total is 139.7M

2) 

I want the total opening balance to be of the current month, and also the opening balance of 2020 should be of the current month,ie. 16M

Formula:

IF (

CurrentMonth = SELECTEDVALUE ( 'Date'[MonthOfYear] )

&& CurrentYear = VALUE ( SELECTEDVALUE ( 'Date'[Year] ) ),[TB Amount Total],[Cumulative Balance Updated]-[Balance Current Month Updated])

RETURN

Result

What changes in the formula is required?

[dedelman_clng]

Hi @Antmkjr  - 

****WARNING - DISCLAIMER FOR THIS SOLUTION*****

I can give you formulas for the specific things that you are asking for, but I'm not sure how helpful they will be in the long run, as they will likely not work at the month level. If you can describe in a narrative, what it is the report is doing, we might be able to find a better way to do it.

****WARNING - DISCLAIMER FOR THIS SOLUTION*****

One thing to know is that Total in PowerBI is nothing like Total in an Excel pivot table. Total evaluates the measure in the context of the subtotal or grand total where it sits (in this case, over all years).  So when your code for Funding says:

Funding = IF([Balance Current Month]<0,[Balance Current Month]*-1)

[Balance Current Month] is evaluated at the "Total" context, and therefore so is Funding. It is not a SUM of the consituent rows/columns - it is re-evaluated.

We can get around that with HASONEVALUE

Funding =
IF (
    HASONEVALUE ( 'Date'[Year] ),   //This is calculating at the year level
    IF ( [Balance Current Month] < 0, [Balance Current Month] * -1 ) + 0,
    CALCULATE (                     //This is calculating at the "Total" level
        SUMX (
            FILTER ( VALUES ( 'Date'[Year] ), [Balance Current Month] < 0 ),
            [Balance Current Month] * -1
        )
    )
)

Somewhat similarly with Opening Balance - your code has a month and a year, but in this visual you only have a year.  So since it is September, TODAY() gives 9 for the month.  But MONTH(SELECTEDVALUE(Date[Year]) in 12, so you're not getting a match.

This code removes the check on month and puts 16M where you want it to be.

Opening Balance = 
VAR TimeTravel =
    CALCULATE ( MAX ( 'Date'[Date] ), PARALLELPERIOD( 'Date'[Date], -1, MONTH ) )
VAR CurrentDate =
    TODAY ()
VAR CurrentMonth =
    MONTH ( CurrentDate )
VAR CurrentYear =
    YEAR ( CurrentDate )
VAR Result =
 IF (
             CurrentYear = VALUE ( SELECTEDVALUE ( 'Date'[Year] ) ),
             [TB Amount Total],[Cumulative Balance Updated]-[Balance Current Month Updated])
RETURN
    Result

Again, please note the disclaimer at the top.

Hope this helps

David

[Antmkjr]

@dedelman_clng 

What I am trying to do is calculating Forecasted Payments, Forecasted Collections, Forecasted Opening, Funding and Forecasted Closing Balances.

Current Balance is TB Balance = Opening Balance of Current Month

Current Month Closing Balance = Opening Balance of Current Month +forecasted Collection -Forecasted Payments 

If Current Month Closing Balance is negative , we add an equal amount of Funding

So Current Month Closing Balance updated = Opening Balance of Current Month +forecasted Collection -Forecasted Payments +Funding

And we Cumulate this vCurrent Month Closing Balance updated to get the actual Closing balance across months , which drill up into years

1)

The formulas should work even when drilled down into the month level.

When drilled down into Sep 2020, the opening balance should be the TB Amount total( Current Trial Balance), which is coming correctly in my file.(Screenshot 1)

2)

But When rolling up into the year then 2020 opening balance should be also of current month, but currently is showing 0M,

this should be 16M(which is current month opening balance = TB Amount Total), also the total opening balance should be 16M in the above screen(and even when drilled down into the month level, the total opening balnce should be of the current month = TB Amount Total =16 M)(Screenshot 2)

3)

Also all the formulas should work when drilling into a single year(even the new cumulative balance updated, which I now realized that is not working- Screenshot 3)

[dedelman_clng]

@Antmkjr - please try the suggestions I made already and see if you can take it from there. If not, please share *updated* pbix for further assistance.