Day 19: Power BI — Finance Analytics Use Cases

P&L data structure: a table with Account (Revenue, COGS, Gross Profit, Operating Expenses, EBIT, Net Profit), Period, and Amount. Use a Matrix visual: rows = Account hierarchy, columns = Month or Quarter, values = Amount. Apply conditional formatting — green for positive, red for negative. Add a "vs Prior Year" column using SAMEPERIODLASTYEAR. For a proper P&L hierarchy, use a parent-child relationship in the Account table and DAX PATH functions to flatten it. The challenge: P&L rows have different signs (revenue is positive, expenses are negative) — ensure measures handle sign conventions correctly.

Actual Revenue = SUM(Actuals[Revenue]) Budget Revenue = SUM(Budget[Revenue]) Variance = [Actual Revenue] - [Budget Revenue] Variance % = DIVIDE([Variance], [Budget Revenue]) Positive variance on revenue = favourable (actual > budget). Negative variance on cost = favourable (actual < budget — spent less than planned). Apply conditional formatting: green for favourable variance, red for unfavourable. Show in a matrix with rows = Cost Centre/Department, columns = Month, values = Actual, Budget, Variance, Variance %. This is a standard finance dashboard requirement.

Cash flow categories: Operating, Investing, Financing (and Net Cash Flow). Structure data with: Date, Category, Sub-Category, Amount (positive for inflows, negative for outflows). Waterfall chart works perfectly for cash flow — shows opening balance, adds, subtracts, and closing balance. Measures: Cash Inflow = CALCULATE(SUM(CashFlow[Amount]), CashFlow[Amount] > 0). Cash Outflow = CALCULATE(SUM(CashFlow[Amount]), CashFlow[Amount] < 0). Net Cash Flow = [Cash Inflow] + [Cash Outflow] (outflow is negative so addition gives net). Running balance using running total pattern.

Approach: store all transaction amounts in the source currency. Create a Currency Exchange Rate table with columns: FromCurrency, ToCurrency, ExchangeDate, Rate. Link to a Date table. Create a measure: Reporting Currency Amount = SUMX(Transactions, Transactions[Amount] * RELATED(ExchangeRates[Rate])). Add a slicer for the reporting currency (USD, INR, GBP). CALCULATE with the selected currency filter applies the right exchange rate. For daily rate fluctuations, the Date dimension connects transactions to the rate on their transaction date.

Working Capital = Current Assets - Current Liabilities. Days Sales Outstanding (DSO) = DIVIDE([Accounts Receivable], [Revenue]) * 365. Shows how many days it takes to collect payment. Days Payable Outstanding (DPO) = DIVIDE([Accounts Payable], [COGS]) * 365. Shows how many days to pay suppliers. Inventory Days = DIVIDE([Inventory], [COGS]) * 365. Cash Conversion Cycle = DSO + Inventory Days - DPO. Build these as cards with trend sparklines and month-over-month comparison. Lower DSO and Inventory Days = better cash flow. Higher DPO = longer to pay suppliers (can be good for cash flow management).