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
jaco1951
Helper III
Helper III

how to simulate a forecasted cash balance

Hi

 

I have a 5 week cash flow forecast report that I need to create, but I cannot find a sustainable way to set it up. 

I have multiple sources, basically it is three groups of values:

1. StartBalance
2. Incoming

3. Outgoing

 

And I have categorized the weeks 1 to 5

 

My challenge is that I want to show a forecast within a table, but my value Startbalance is only to find correct in week 1 as one of my sources do not have any other data than actual balance (myBank source). 

I somehow need to drag this value along as I add incoming and subtract outgoing.... Guess it is a simple task for those who know...

This is what I have today:
1.JPG

And sorted by datasource it looks like this:
2.JPG

 

And from my excel sheet I have created what I actually would like to get:

3.JPG

 

My datafile is uploaded here:
Excel test data

I would appreciate if someone could assist me here. 

Best regards

Espen

 

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @jaco1951

As far as I understand, you want to use the value for Startbalance in week 1 to forecast values for Startbalance in following week, so that you can get this matrix as the picture shown.

look at the first picture and the third, why the values for “outgoing” from week 3 to 5 is different, though I use the data in picture 1 and get the following picture.

Here is my pbix for your reference.

8.png

 

 

Best Regards

Maggie

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

 

There is no file at that link.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-juanli-msft
Community Support
Community Support

Hi @jaco1951

As far as I understand, you want to use the value for Startbalance in week 1 to forecast values for Startbalance in following week, so that you can get this matrix as the picture shown.

look at the first picture and the third, why the values for “outgoing” from week 3 to 5 is different, though I use the data in picture 1 and get the following picture.

Here is my pbix for your reference.

8.png

 

 

Best Regards

Maggie

Anonymous
Not applicable

@v-juanli-msft I am trying to solve the same issue but cannot find your file.  would you mind correcting the link?

Dear Maggie @v-juanli-msft 

 

I have tried to replicated your solution, but as my data is more complex than in my example I struggle with getting the calculated columns to work. My first challenge seems to occur in Column1, where I need to use much more columns in the ALLEXCEPT function. I am not able to sum the values in the correct granularity. I am supposed to sum values for each "currentweekorgreater" and "amount_category". But I'm lost....

Capture.JPG

I apologize for all the scrambling, but these data are sensitive. 

Below is what you have created in your solution, and as you see the expression you are using with ALLEXCEPT is much more simple and works fine, but if I use only those two parameters I'm not even close to fetching the correct values....


Capture2.JPG

 

Br Espen

Hi @jaco1951

" I am supposed to sum values for each "currentweekorgreater" and "amount_category""

Since you only need sum values for each "currentweekorgreater" and "amount_category", you don't need add such columns in the "ALLEXCEPT" function.

"ALLEXCEPT" in my formula means to clear all filters except "currentweekorgreater" and "amount_category".

 

Best Regards

Maggie

Thank you Maggie,

I have trimmed my data and created a table that is minimized, no currency, company etc.

It is a successful dataset build from your solution. I will now proceed to add some more granularity and see if I can make it work, I have spend quite some time to understand the logic in your solution, and I think I have it all under control 🙂

Thanks again.

 

Br Espen

Dear Maggie

 

Thank you for your solution. I have tried to study it, and I see that there are quite a lot of extra columns to create this calculation. Is it so that this cannot be solved without doing so?

I have around 30 tables in my data load and large data sets. I will see if I can implement this and how it will affect the overall load of data.

 

You'll hear from me when I have tested.

 

Thanks.

 

Br Espen

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.