Cash Flow

[rogeriotortosaParticipant Started: 6Replies: 12Total: 18]

Hi,

Hello
I need to set up a cash flow with the following tables
DimSaldoCC – final balance of each month
DimCalendar – calendar
Fact – Movement daily, classified by type of Revenue and Expense

All tables are related by date

The initial movement balance of one month and equal to the final balance of the preceding month

Example of visualization we need

Filter month = X

Day 1 day 2 day 3 ….. day 31
Opening Balance 100.00 120.00 60.00
Expense 1 / revenue 1 -40.00 +20.00 -90.00
Expense “n” / revenue “n” +60.00 -80.00 +20.00
Closing balance 120.00 60.00 -10.00

Opening balance day 1 / x is equal to the final balance in the dimSaldoCC table of the previous month

The final balance of day 2 / x equals the opening balance of day 1 / x + Revenue – Expenses, ie the final balance of day 1 / x

So until the last day of the month

In the example in annex (xlsx) we need to put in the first line the initial balance and in the last line the final balance
https://innersystem-my.sharepoint.com/personal/rogerio_prodatasystems_com_br/_layouts/15/guestaccess.aspx?docid=198948e9c4e9e4b02886ab25c24fbe332&authkey=AdX5mHl9k3ymDHfdNnD8MYg

Att.

Attachments:

You must be logged in to view attached files.

[rogeriotortosaParticipant Started: 6Replies: 12Total: 18]

hello

[peterhof3Participant Started: 0Replies: 2Total: 2]

Hi! I am actually struggling with the same thing. Did you manage to find a solution to this.

I have tried to use PM Sales:= ([Sales], Dateadd(‘Calendar'[FullDate], -1 , Month)) , but this only seems to give me the Sum of all sales for the year.

[peterhof3Participant Started: 0Replies: 2Total: 2]

Rogerio;

Have a look at these two blogs:

https://powerpivotpro.com/2011/12/cash-flow-statement-in-powerpivot/

also here

https://powerpivotpro.com/2011/12/the-greatest-formula-in-the-world-part-one/ this one resolved my issue in getting the previous month’s data (you have to fiddle it a bit).

HTH

[Author]

Posts