Forum Discussion

RogerioTortosa's avatar
RogerioTortosa
Helper II
9 years ago

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, Da...
Need Help
RogerioTortosa's avatar
RogerioTortosa
Helper II
9 years ago

Hi, 

 

The client does not allow the information to be released

But follows tables and relationships

 

 

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's avatar
Anonymous
Not applicable
9 years ago

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]

 

I will try to explain simpler with an excel example:

 

 

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!

  • RogerioTortosa's avatar
    RogerioTortosa
    Helper II
    9 years ago

    DimTipFlu.idTipFlu will probably be your PaymentType

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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.