GP App-Power BI Report

Requirement Document for GP App-Power BI Report

1. Purpose

The Power BI report is designed to provide an interactive, data-driven overview of settlement cases, entities, and financial performance within a defined reporting period. It is used to track and analyze settlement trends, financial statuses, and performance metrics for various entities involved in the settlement process.

2. Scope

The report covers data on settlement cases, financials, and entity performance. It includes metrics like settlement amounts, advance statuses, and key financial indicators such as internal rate of return (IRR), loss-on-principal (LOP), and days-on-fund (DOF). The report is designed to be used by financial analysts, business managers, and stakeholders interested in the performance of settlement funds and related entities.

3. Key Data Points

The report features several critical data points, categorized as follows:

Settlement Cases:
- Case ID
- Case Type (Pre/Post)
- Combined Name
- Current Law Firm
- Salesperson
- Date Funded
- Settlement Amount
- Settlement Status (Naturally Settled, Full Payment, Write-off, Some Loss)
Entity-Level Metrics:
- Entity Name (e.g., 25 Plaza Street, 5-Star)
- Entity Basis Contract
- Settlement Amount
- Internal Rate of Return (IRR)
- Days-on-Fund (DOF)
- Loss on Principal (LOP)
- Advance Entity Basis
- Timestamped CV
- Application Fees, Shipping, Wire/ACH Fees
Financial Performance:
- Total Advance Amount
- Settlement Amount Due
- Write-Off and Discount Rates
- Internal Rate of Return (IRR)
- Breakdown by Funding Year and Settlement Date

4. Report Features

The report contains interactive elements that allow the user to filter and drill down into specific data:

Filters:
- Date filters (Natural Settlement Date, Date Funded)
- Entity Filters (Combined Name, Current Law Firm, Final Salesperson)
- Status Filters (Advance Status, Pre/Post Case Type)
- Owner Entity
- Securitization Date
Charts and Tables:
- Financial metrics by year and funding duration (e.g., Years 0-10)
- IRR and financial returns over time
- Entity-level financial breakdowns (e.g., Advance Active Entity Basis, Timestamped CV)
- Settlement trends and loss ratios (e.g., Discounted, Full Payment, Write-off)

5. User Interactions

The report provides dynamic filtering and drill-through capabilities for detailed analysis:

Click on specific entities or law firms to drill into their performance over a period.
Filters enable comparison across multiple entities based on settlement amounts, performance ratios, and financial return metrics.
The ability to view aggregate financial metrics across all entities and cases.

6. Data Sources

The report is built on data from case management and financial systems that track settlements, fundings, and entity performance over time.

Settlement Records: Includes data on funded cases, settlement amounts, and final outcomes (e.g., naturally settled, discounted, etc.).
Entity-Level Financials: Provides insight into the financial performance of each entity involved in settlements.

7. Metrics and KPIs

The following Key Performance Indicators (KPIs) are included in the report:

Settlement Amount: Total settlement amount across all cases.
Days-on-Fund (DOF): The number of days between funding and settlement.
Internal Rate of Return (IRR): The profitability of investments in settlement cases.
Loss on Principal (LOP): The percentage of losses relative to the principal amount.
Advance Status: Current status of advances (e.g., Active, Settled).
Natural Settlement: The amount settled naturally without loss or write-off.
Write-off Rate: The percentage of cases written off due to settlement failure.

8. Reporting Frequency

The report is designed to be updated on a periodic basis (e.g., daily, weekly, or monthly) depending on the needs of the users. It tracks settlements over a specified date range, with options for filtering based on the period of interest.

9. Technical Specifications

Platform: Power BI Desktop
Data Connection: Secure database connection to financial and case management systems.
Data Refresh: Scheduled data refresh to ensure up-to-date reporting on entity financials and settlements.

10. User Roles

Financial Analysts: Will use the report to assess the performance of settlement cases, entities, and financial returns.
Business Managers: Monitor overall business health, identifying areas of loss and opportunities for improving return on settlements.
Stakeholders: Interested in overall financial performance and strategic insights based on settlement data.

11. Limitations and Assumptions

Data Accuracy: The report assumes accurate and timely data from underlying systems. Any discrepancies in source data will impact the accuracy of the report.
Update Frequency: The report is dependent on periodic data refreshes to provide up-to-date information. The accuracy of KPIs is contingent on the latest data being available.

Reporting View GP App _ Updated Download

1. Data Extraction & Loading (SQL Queries)

The first step in building the Power BI report involves extracting relevant data from the source database using SQL queries. The queries pull settlement case details, entity financials, and key metrics.

1.1 Extracting Settlement Case Data

sqlCopySELECT  
    CaseID,  
    CaseType,  
    CombinedName,  
    CurrentLawFirm,  
    SalesPerson,  
    FinalSalesperson,  
    DateFunded,  
    SettlementAmount,  
    Status  
FROM SettlementCases  
WHERE DateFunded BETWEEN '2006-01-01' AND '2022-09-02';

This query retrieves all settlement case records within the defined reporting period.

1.2 Extracting Financial Entity Data

sqlCopySELECT  
    EntityName,  
    EntityBasis,  
    SettlementAmount,  
    IRR,  
    LOP_Percentage,  
    DOF_Percentage  
FROM FinancialEntities  
WHERE EntityStatus = 'Active';

This query fetches financial metrics for active entities.

1.3 Extracting Natural Settlement Data

sqlCopySELECT  
    OwnerEntity,  
    BasisAmount,  
    SettlementAmount,  
    SettlementDate,  
    SettlementType  
FROM SettlementFinancials  
WHERE SettlementType IN ('Discounted', 'Full Payment', 'Some Loss', 'Write-Off');

This ensures settlement data is categorized into appropriate financial types.

2. Data Transformation Using Power Query (M Language)

After loading data into Power BI, Power Query is used to clean, transform, and prepare the data for reporting.

2.1 Data Cleaning & Formatting

Remove null and duplicate values:

mCopyFilteredRows = Table.SelectRows(Source, each ([SettlementAmount] <> null))  
DistinctRows = Table.Distinct(FilteredRows)

Standardizing Date Format:

mCopyFormattedDate = Table.TransformColumns(DistinctRows, {{"SettlementDate", DateTime.FromText, type date}})

Replacing Missing Values:

mCopyReplaceNulls = Table.ReplaceValue(FormattedDate, null, 0, Replacer.ReplaceValue, {"SettlementAmount"})

2.2 Merging Related Tables

mCopyMergedTable = Table.NestedJoin(SettlementCases, "CaseID", SettlementFinancials, "CaseID", "MergedCases", JoinKind.LeftOuter)  
ExpandedTable = Table.ExpandTableColumn(MergedTable, "MergedCases", {"SettlementAmount", "SettlementDate"})

This merges financial settlement data with case details.

2.3 Adding Calculated Columns

Days on Fund Calculation:

mCopyDOF_Calculated = Table.AddColumn(ExpandedTable, "DaysOnFund", each Duration.Days([SettlementDate] - [DateFunded]), Int64.Type)

Categorizing Settlement Status:

mCopyStatusCategorization = Table.AddColumn(DOF_Calculated, "SettlementCategory",  
    each if [DOF] < 180 then "Short-Term"  
         else if [DOF] < 365 then "Medium-Term"  
         else "Long-Term")

3. Data Modeling & Calculation Using DAX

Once data is cleaned and structured, DAX (Data Analysis Expressions) is used to create calculated measures and KPIs.

3.1 Key Performance Indicators (KPIs)

Total Settlement Amount

DAXCopyTotal_Settlement = SUM(SettlementCases[SettlementAmount])

Average IRR (Internal Rate of Return)

DAXCopyAvg_IRR = AVERAGE(FinancialEntities[IRR])

Days on Fund Calculation

DAXCopyDaysOnFund = DATEDIFF(SettlementCases[DateFunded], SettlementCases[SettlementDate], DAY)

Loss on Principal (LOP) Percentage

DAXCopyLOP_Percentage = DIVIDE(SUM(SettlementFinancials[SettlementAmountDue]), SUM(SettlementFinancials[BasisAmount]), 0) * 100

3.2 Dynamic Time Intelligence Measures

YTD Settlement Amount

DAXCopyYTD_Settlement =  
    CALCULATE(  
        SUM(SettlementCases[SettlementAmount]),  
        DATESYTD(SettlementCases[SettlementDate])  
    )

Previous Year Settlement Comparison

DAXCopyPrevYear_Settlement =  
    CALCULATE(  
        SUM(SettlementCases[SettlementAmount]),  
        SAMEPERIODLASTYEAR(SettlementCases[SettlementDate])  
    )

Settlement Trend Over Time

DAXCopySettlementTrend =  
    CALCULATE(  
        SUM(SettlementCases[SettlementAmount]),  
        DATESINPERIOD(SettlementCases[SettlementDate], MAX(SettlementCases[SettlementDate]), -12, MONTH)  
    )

4. Report Visualization in Power BI

After defining measures, the final step is to build visualizations and interactive reports in Power BI.

4.1 Dashboard Elements

Tables: Display settlement cases, entity metrics, and financial details.
Bar Charts: Show settlement trends over time (e.g., year-wise funding and settlement).
KPI Cards: Display total settlement, IRR, LOP%, and DOF%.
Line Charts: Illustrate financial performance trends over time.
Slicers & Filters: Enable users to filter by entity, date, settlement type, and law firm.

4.2 Interactive Features

Drill-through reports: Clicking on an entity provides detailed financial metrics.
Conditional Formatting: Highlight high IRR entities in green, low IRR in red.
Bookmarks & Buttons: Allow toggling between summary and detailed views.

5. Deployment & Data Refresh

5.1 Data Refresh Setup

Configure automatic refresh in Power BI Service.
Schedule refresh frequency (e.g., daily, weekly) in Power BI settings.
Ensure SQL queries and Power Query transformations refresh correctly.

5.2 Access Control & Security

Role-based access: Different user roles (e.g., Analyst, Manager, Admin) get different report views.
Row-level security (RLS): Ensure users can only see data relevant to their role.
Data validation: Regularly check for missing or incorrect data.

6. Conclusion

This Power BI report project follows a structured approach:

Extract data using SQL queries to pull relevant case and financial information.
Transform data with Power Query to clean, merge, and structure it.
Create DAX measures to compute KPIs like IRR, LOP%, and settlement trends.
Build Power BI visualizations with dynamic filters and drill-through features.
Deploy and schedule data refresh to ensure up-to-date reporting.

Excel (.xlsx) format, containing three key datasets:

SettlementCases – Case details with settlement information.
FinancialEntities – Entity-level financial performance data.
SettlementFinancials – Settlement transactions, including basis amounts and IRR.

Settlement_Report Download