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
RogerioTortosa
Helper II
Helper II

Cash Flow

have a doubt that I still can not solve by watching your classes.
 
How do you set up a type of cash flow in PBI?

 

Example:
Tables: Calendar, Initial and Monthly Balance of the Bank Account, Daily Financial Movement

 

                                                            Day 1     day 2      day 3 ..... day 31

Opening Balance                                  100.00    120.00      60.00
Category of Expenses and revenues.       -40,00   +20,00 -    90,00
Category of Expenses and revenues.      +60.00    -80.00    +20.00
Closing balance                                    120.00     60.00     -10.00

 

att.

26 REPLIES 26
RogerioTortosa
Helper II
Helper II

How to calculate the final balance on 01/01/2017?
How to calculate the opening balance on 02/01/2017, what is the final balance on 01/01/2017?

 

fig_2.png

 

 

 

In the Zip file = pbix e data source (xlsx)

 

 

 

 

 

 

 

CheenuSing
Community Champion
Community Champion

Hi @RogerioTortosa

 

Check if this link is of help 

 

http://pakaccountants.com/cash-flow-summary-excel-pivot-tables-multiple-worksheets/

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi @RogerioTortosa

 

I went through this link 

https://www.powerpivotpro.com/2010/06/building-a-cash-flow-statement-in-powerpivot-using-dynamic-mea...

 

and it worked for me without any errors.

 

The only caveat is the funds flow is separate and the opening and closing is separate visuals.

 

If this works for you please give KUDOS.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Is there a way to get the funds flow with the opening and closing on same visual? 

yes, it possible

 

Test = if ( DAY ( MIN ( dimCalendario[data] ) ) = 1; if ( MIN ( dimTipFlu[Open_Bank] ) = 1; SUM ( fatMovi[vlrmovi] ); IF ( MIN ( dimTipFlu[Close_Bank] ) = 1; CALCULATE ( SUM ( fatMovi[vlrmovi] ); ALL ( dimTipFlu ) ); SUM ( fatMovi[vlrmovi] ) ) ); IF ( MIN ( dimTipFlu[Open_Bank] ) = 1; CALCULATE ( TOTALMTD ( SUM ( fatMovi[vlrmovi] ); PREVIOUSDAY ( dimCalendario[data] ) ); ALL ( dimTipFlu ) ); IF ( MIN ( dimTipFlu[Close_Bank] ) = 1; CALCULATE ( TOTALMTD ( SUM ( fatMovi[vlrmovi] ); dimCalendario[data] ); ALL ( dimTipFlu ) ); SUM ( fatMovi[vlrmovi] ) ) ) )

Using the data set for this link:

 

https://www.microsoft.com/en-us/download/details.aspx?id=38838

 

How could I create a cash flow flow similar to the picture shown below with Power BI?

 

 

 

Example.JPG

 

Please let me know if this is possible and how we could get it to work.

 

Thank  you 

Rogerio, ficou muito bom seu Fluxo de Caixa. Teria como compartilhar um PBIX modelo?

Hi, 

 

The client does not allow the information to be released

But follows tables and relationships

 

relacionamento.pngrelacoes.png

 

Vlr Movi =
if (
    DAY ( MIN ( dimCalendario[data] ) ) = 1;
    if (
        MIN ( dimTipFlu[Open_Bank] ) = 1;
        SUM ( fatMovi[vlrmovi] );
        IF (
            MIN ( dimTipFlu[Close_Bank] ) = 1;
            CALCULATE ( SUM ( fatMovi[vlrmovi] ); ALLSELECTED(dimTipFlu) );
            SUM ( fatMovi[vlrmovi] )
        )
    );
    IF (
        MIN ( dimTipFlu[Open_Bank] ) = 1;
        CALCULATE (
            TOTALMTD ( SUM ( fatMovi[vlrmovi] ); PREVIOUSDAY ( dimCalendario[data] ) );
            ALL ( dimTipFlu )
        );
        IF (
            MIN ( dimTipFlu[Close_Bank] ) = 1;
            CALCULATE (
                TOTALMTD ( SUM ( fatMovi[vlrmovi] ); dimCalendario[data] ); ALLSELECTED(dimTipFlu)
            );
            SUM ( fatMovi[vlrmovi] )
        )
    )
)

Anonymous
Not applicable

Hello, I'm new to Power BI. I want to make Cashflow in Power BI. I have some expenses and revenue by date in Power BI - matrix report. It look like this:

 

I have only one table - vmCashPaymentOrders and two important fields: Due_Date and Flow_BGN.

I have put the date in the columns and data in the rows.  I want the following thing:

day by day to see previous day sum(end balance) of fiеld vmCashPaymentOrders[Flow_BGN] as opening balance for the next day.

I aslo made a calendar table called DateTable, with a filed DateTable[Date]

 Balance.jpg

I will try to explain simpler with an excel example:

 

flow2.jpg

 

I want to try your formulas, but I'm not sure if I undestand them correctly, so can you please adopt formula to my tables. I could't understand what is dimTipFlu[Close_Bank] and dimTipFlu[Open_Bank] - data in your table or measures?

Thanks in advance!

DimTipFlu.idTipFlu will probably be your PaymentType

In Fact we have the opening and closing movement of the account balance

Anonymous
Not applicable

Please explain what are this fields in your tables:

dimCalendario[data]

dimTipFlu

dimTipFlu[Open_Bank]

dimTipFlu[Close_Bank] 

fatMovi
fatMovi[vlrmovi]

 

This fields dimTipFlu[Open_Bank] and dimTipFlu[Close_Bank] confuse me.

I thinks this information, as well as the measure, will give me the answer.

 

Thank you Rogerio.  Looks pretty close.  How did you structure the dax to show the start balance and end balance?  The end balance from one column should be the the start balance on the next.    

Cashflow.JPG

 

 

 

 

Test = if ( DAY ( MIN ( dimCalendario[data] ) ) = 1; if ( MIN ( dimTipFlu[Open_Bank] ) = 1; SUM ( fatMovi[vlrmovi] ); IF ( MIN ( dimTipFlu[Close_Bank] ) = 1; CALCULATE ( SUM ( fatMovi[vlrmovi] ); ALL ( dimTipFlu ) ); SUM ( fatMovi[vlrmovi] ) ) ); IF ( MIN ( dimTipFlu[Open_Bank] ) = 1; CALCULATE ( TOTALMTD ( SUM ( fatMovi[vlrmovi] ); PREVIOUSDAY ( dimCalendario[data] ) ); ALL ( dimTipFlu ) ); IF ( MIN ( dimTipFlu[Close_Bank] ) = 1; CALCULATE ( TOTALMTD ( SUM ( fatMovi[vlrmovi] ); dimCalendario[data] ); ALL ( dimTipFlu ) ); SUM ( fatMovi[vlrmovi] ) ) ) )

Hi @RogerioTortosa,

 

Can you please explain what the formula does and also share a link to down load the pbix file.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

How to calculate the final balance on 01/01/2017?
How to calculate the opening balance on 02/01/2017, what is the final balance on 01/01/2017?

fig_2.png

 

 

In the Zip file = pbix e data source (xlsx)

 

 

Power BI is more for creating dashboards.  While DAX is great for creating measures and can do a lot, in your example it might be easier to do the cash flow in excel and just import the data to Power BI to show as a table.  Power BI is not as flexible for creating cell by cell calculations.  

 

However, if you could provide a small sample of your data (anonymized if necessary) we can try to help with a solution

A PBIX file linked to your database will not help.  Could you create a small Excel file with a subset of the data?

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.