1. Objective
Develop a Power BI report “Cash Flow (CF)” that collects data from multiple tables, calculates key metrics, and displays them for the selected period (day/week/month), with the ability to drill down by subcategory.
2. Data Sources
Regular Expenses: Date, Expense Category, Subcategory, Amount, Payment Description, Status, Norm, …
Irregular Expenses: same fields
Supplier Expenses: same fields
Transportation Services: same fields
Receipts: Date, Receipt Category, Subcategory, Amount, Description, Status, Norm, …
Balances: Date, Balance Type (Opening/Closing), Account, Account Type, Balance
All tables are available in a shared storage (CSV/Excel/SQL).
3. Data Model and Transformations
Import all tables into Power BI Desktop.
Ensure correct data types:
Date → Date
Amount → Decimal
Normalize field names and values: consistent casing, no extra spaces.
Append the four expense tables into a single table “AllExpenses.”
Append receipts into “AllReceipts.”
Build a single table “CF_RawData” by combining:
Rows from “AllExpenses” (tagged as Expense)
Rows from “AllReceipts” (tagged as Receipt)
Rows from “Balances” (using Balance Type = Opening/Closing)
Calendar table: create a Date dimension with Day, Week, Month, Quarter, Year fields.
5. Visualization
Matrix/Table with hierarchical rows:
Level 1: Period (Day/Week/Month)
Level 2: Category → Subcategory
Columns:
Opening Balance
Total Revenue
Total Receipts
Total Payments
Closing Balance
Slicers:
Period selector (Day/Week/Month)
Category/Subcategory filter
Status filter (Paid/Unpaid)
6. Conditional Formatting
Status ≠ “Paid” → red font in the Status column.
Norm = “NOT OK” → red cell border around the Norm column.
Use Power BI’s conditional formatting rules on the corresponding fields.
7. Subcategory Drill-Down
When drilling into Subcategory, aggregate all five metrics at the subcategory level.
Enable drill-down by clicking a Category to expand its Subcategories.
8. Non-Functional Requirements
Performance: report refresh time ≤ 10 seconds with 50 000 input rows.
Scalability: easy to add new expense or receipt tables with minimal model changes.
Documentation: include a README explaining all transformations, DAX measures, and visual configurations.
The project is very similar to https://freelancehunt.com/project/excel-financial-report/1515026.html but should be done in PowerBI