Home Forums Power BI Cash Flow

This topic contains 3 replies, has 2 voices, and was last updated by  peterhof3 3 years, 6 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #7342

    rogeriotortosa
    Participant
    • Started: 6
    • Replies: 12
    • Total: 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.
    #7497

    rogeriotortosa
    Participant
    • Started: 6
    • Replies: 12
    • Total: 18

    hello

    #8164

    peterhof3
    Participant
    • Started: 0
    • Replies: 2
    • Total: 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.

    #8165

    peterhof3
    Participant
    • Started: 0
    • Replies: 2
    • Total: 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

Viewing 4 posts - 1 through 4 (of 4 total)

The forum ‘Power BI’ is closed to new topics and replies.