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
  • fatherof4's avatar
    fatherof4
    Frequent Visitor
    9 years ago

    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.    

     

     

     

     

    • RogerioTortosa's avatar
      RogerioTortosa
      Helper II
      9 years ago

      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] ) ) ) )

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi RogerioTortosa,

         

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

         

         

        Cheers

         

        CheenuSing

  • escolastico's avatar
    escolastico
    Regular Visitor
    9 years ago

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

    • 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!