This topic contains 3 replies, has 2 voices, and was last updated by peterhof3 6 years, 8 months ago.
-
AuthorPosts
-
January 23, 2017 at 5:52 pm #7342
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 ExpenseAll 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.00Opening 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=AdX5mHl9k3ymDHfdNnD8MYgAtt.
Attachments:
You must be logged in to view attached files.February 2, 2017 at 5:39 pm #7497hello
July 18, 2017 at 5:06 pm #8164Hi! 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.
July 18, 2017 at 8:42 pm #8165Rogerio;
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
-
AuthorPosts
The forum ‘Power BI’ is closed to new topics and replies.