Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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?
In the Zip file = pbix e data source (xlsx)
Check if this link is of help
http://pakaccountants.com/cash-flow-summary-excel-pivot-tables-multiple-worksheets/
Cheers
CheenuSing
I went through this link
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
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?
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
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] )
)
)
)
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!
DimTipFlu.idTipFlu will probably be your PaymentType
In Fact we have the opening and closing movement of the account balance
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.
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
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?
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |