Power BI - Essential Finance DAX Formulas

on

 

 Essential Finance DAX Formulas                            

1. Revenue

Total Revenue = SUM(Sales[Revenue])

Revenue YTD = 
TOTALYTD(
    [Total Revenue],
    DateTable[Date]
)

Revenue LY = 
CALCULATE(
    [Total Revenue],
    SAMEPERIODLASTYEAR(DateTable[Date])
)

Revenue Growth % = 
DIVIDE([Revenue YTD] - [Revenue LY], [Revenue LY], 0)

 Tracks revenue, year-to-date totals, last year comparison, and growth percentage.

2. Expenses

Total Expenses = SUM(Expenses[Amount])

Expenses YTD = 
TOTALYTD(
    [Total Expenses],
    DateTable[Date]
)

Expense Variance = 
[Total Expenses] - [Budgeted Expenses]

Monitors costs and compares against budget.

3. Profitability

Gross Profit = [Total Revenue] - [COGS]

Net Profit = [Total Revenue] - [Total Expenses]

Profit Margin % = 
DIVIDE([Net Profit], [Total Revenue], 0)

Net Profit YTD = 
TOTALYTD(
    [Net Profit],
    DateTable[Date]
)

 Shows profitability and margins over time.

4. Cash Flow

Cash Inflows = SUM(CashFlow[Inflow])
Cash Outflows = SUM(CashFlow[Outflow])

Net Cash Flow = [Cash Inflows] - [Cash Outflows]

Cash Flow YTD = 
TOTALYTD(
    [Net Cash Flow],
    DateTable[Date]
)

 Tracks liquidity and cash movement.

5. Accounts Receivable (AR) & Payable (AP)

Outstanding AR = SUM(AR[InvoiceAmount]) - SUM(AR[Payments])
Outstanding AP = SUM(AP[InvoiceAmount]) - SUM(AP[Payments])

DSO (Days Sales Outstanding) = 
DIVIDE([Outstanding AR], [Total Revenue]/30, 0)

DPO (Days Payable Outstanding) = 
DIVIDE([Outstanding AP], [Total Expenses]/30, 0)

 Measures collection and payment efficiency.

6. Budget vs. Actuals

Budget Variance = [Total Revenue] - [Budgeted Revenue]

Budget Variance % = 
DIVIDE([Budget Variance], [Budgeted Revenue], 0)

Budget Variance YTD = 
TOTALYTD(
    [Budget Variance],
    DateTable[Date]
)

 Tracks how actuals compare against planned budgets.


Finance DAX Formulas with Date Intelligence

1. Revenue

Total Revenue = SUM(Sales[Revenue])

Revenue MTD = 
CALCULATE(
    [Total Revenue],
    DATESMTD(DateTable[Date])
)

Revenue YTD = 
TOTALYTD(
    [Total Revenue],
    DateTable[Date]
)

Revenue LY = 
CALCULATE(
    [Total Revenue],
    SAMEPERIODLASTYEAR(DateTable[Date])
)

Revenue Growth % = 
DIVIDE([Revenue YTD] - [Revenue LY], [Revenue LY], 0)

 Tracks revenue month-to-date, year-to-date, last year, and growth percentage.

2. Expenses

Total Expenses = SUM(Expenses[Amount])

Expenses MTD = 
CALCULATE(
    [Total Expenses],
    DATESMTD(DateTable[Date])
)

Expenses YTD = 
TOTALYTD(
    [Total Expenses],
    DateTable[Date]
)

Expense Variance = 
[Total Expenses] - [Budgeted Expenses]

👉 Monitors costs monthly, yearly, and against budget.

3. Profitability

Gross Profit = [Total Revenue] - [COGS]

Net Profit = [Total Revenue] - [Total Expenses]

Profit Margin % = 
DIVIDE([Net Profit], [Total Revenue], 0)

Net Profit YTD = 
TOTALYTD(
    [Net Profit],
    DateTable[Date]
)

Net Profit LY = 
CALCULATE(
    [Net Profit],
    SAMEPERIODLASTYEAR(DateTable[Date])
)

Shows profitability and margins with year-over-year comparisons.

4. Cash Flow

Cash Inflows = SUM(CashFlow[Inflow])
Cash Outflows = SUM(CashFlow[Outflow])

Net Cash Flow = [Cash Inflows] - [Cash Outflows]

Cash Flow YTD = 
TOTALYTD(
    [Net Cash Flow],
    DateTable[Date]
)

Cash Flow LY = 
CALCULATE(
    [Net Cash Flow],
    SAMEPERIODLASTYEAR(DateTable[Date])
)

 Tracks liquidity and cash movement across time periods.

5. Accounts Receivable (AR) & Payable (AP)

Outstanding AR = SUM(AR[InvoiceAmount]) - SUM(AR[Payments])
Outstanding AP = SUM(AP[InvoiceAmount]) - SUM(AP[Payments])

DSO (Days Sales Outstanding) = 
DIVIDE([Outstanding AR], [Total Revenue]/30, 0)

DPO (Days Payable Outstanding) = 
DIVIDE([Outstanding AP], [Total Expenses]/30, 0)

DSO YTD = 
TOTALYTD(
    [DSO],
    DateTable[Date]
)

DPO YTD = 
TOTALYTD(
    [DPO],
    DateTable[Date]
)

 Measures collection and payment efficiency over time.

6. Budget vs. Actuals

Budget Variance = [Total Revenue] - [Budgeted Revenue]

Budget Variance % = 
DIVIDE([Budget Variance], [Budgeted Revenue], 0)

Budget Variance YTD = 
TOTALYTD(
    [Budget Variance],
    DateTable[Date]
)

Budget Variance LY = 
CALCULATE(
    [Budget Variance],
    SAMEPERIODLASTYEAR(DateTable[Date])
)

 Tracks how actuals compare against planned budgets across periods.

 Takeaway

By combining Finance KPIs with Date Intelligence functions (TOTALYTD, DATESMTD, SAMEPERIODLASTYEAR), you can show trends, comparisons, and growth patterns—making your Finance dashboard not just descriptive but strategic.



Comments

Post a Comment