Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
And sorted by datasource it looks like this:
And from my excel sheet I have created what I actually would like to get:
My datafile is uploaded here:
Excel test data
I would appreciate if someone could assist me here.
Best regards
Espen
Solved! Go to Solution.
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.
Best Regards
Maggie
Hi,
There is no file at that link.
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.
Best Regards
Maggie
@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....
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....
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |