cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JaneSOptus
Frequent Visitor

How to calculate forecast closing balance

Help, please. It's now the fifth days now. I am still unable to solve the proble. Here is the scenario

 

Two columns

 

1. Movment contains actual and forecast movement.

2. Closing balance.

 

Capture.PNG

 

I need to figure how to calculate the forecast closing balance. In excel it is straight forward - previous period closing plus current period movement equal to current period closing balance.

 

I have tried

 

1.  Opening balance of current period = Calculate([closing balance], Previousmonth([Date])

2. Closing balance of current period = [Opening balance of current period]+ [Movement]

 

The above calculation only come up with one month. The calculation doesn't iterate and fill all the forecast period.

 

Really apprecaite your help.

 

Thanks,

Jane

3 REPLIES 3
mehmet
Frequent Visitor
Ashish_Mathur
Super User III
Super User III

Hi,

 

What data do you actually have in your source data file?  Share that data (in a form that can be pasted in MS Excel)


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @JaneSOptus,

 

It seems that you want to calcualte the accumulate value.

 

You could try with the measure below.

 

 

Measure = m =
CALCULATE (
SUM ( 'table'[Movement] ),
FILTER ( ALL ( 'table' ), 'table'[Period] <= MAX ( 'table'[Period] ) )
)
+ 600

Capture.PNG

 

If you still need help, please share your data sample as table and your desired output so that we could help further on it.

 

Best  Regards,

Cherry

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors