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.
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 filed Flow_BGN as opening balance for the next day.
I will try to explain simpler with an excel example:
Thanks in advance!
Solved! Go to Solution.
@Anonymous
In Power BI Desktop, it's not supported to directly edit opening/closing Total. If you want to display a matrix as above screenshot, you need to add OpeningBalance and EndingBalance row within each Date group. So your source data can be like:
Now you need to add an index column in Query Editor first.
Then you can create a calculated column like below:
Flow = IF ( Table2[Flow_BGN] = "Revenue" || Table2[Flow_BGN] = "Expense", Table2[Amount], CALCULATE ( SUM ( Table2[Amount] ), FILTER ( ALL ( Table2 ), Table2[Index] <= EARLIER ( Table2[Index] ) ) ) )
You can put Date as Column group, Flow_BGN as Row group, and put above column into Value in your matrix.
Regards,
@Anonymous
In Power BI Desktop, it's not supported to directly edit opening/closing Total. If you want to display a matrix as above screenshot, you need to add OpeningBalance and EndingBalance row within each Date group. So your source data can be like:
Now you need to add an index column in Query Editor first.
Then you can create a calculated column like below:
Flow = IF ( Table2[Flow_BGN] = "Revenue" || Table2[Flow_BGN] = "Expense", Table2[Amount], CALCULATE ( SUM ( Table2[Amount] ), FILTER ( ALL ( Table2 ), Table2[Index] <= EARLIER ( Table2[Index] ) ) ) )
You can put Date as Column group, Flow_BGN as Row group, and put above column into Value in your matrix.
Regards,
I think I understood most of the description and i also have tried with a new table from excel with all needed rows:
This is the table:
This is the Matrix view, showing right calculations:
Everything looks ok except: How to add OpeningBalance and EndingBalance row within each Date group?
Have in mind that the table vmCashPaymentOrders is from SQL Source(MS SQL), so i don't have this columns by default. The table contains only Revesues and Expenses, Date, Amount and some other not important columns:
Thanks!
Hi everybody
Anybody did this?
About initial balance cash flow?
I've the same problem.
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 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |