Lesson 89 Power BI Advanced DAX Techniques for Financial Analysis

Wlcome back to Virvijay.com 

Financial analysis is one of the most critical use cases for Power BI. To get meaningful insights, you need to use advanced DAX formulas to perform calculations like:

• 📌 Year-over-Year (YoY) and Month-over-Month (MoM) Analysis
• 📌 Running Totals & Cumulative Sales
• 📌 Dynamic Forecasting with DAX
• 📌 Time Intelligence Functions (YTD, MTD, QTD)
• 📌 Advanced Financial KPIs – ROI, Profit Margin, Variance Analysis

Let’s dive deep into Advanced DAX for financial analysis! 📊

1️⃣ Year-over-Year (YoY) and Month-over-Month (MoM) Analysis

Tracking performance over time is crucial for financial reporting. Let’s calculate YoY and MoM percentage changes using DAX.

🔹 Year-over-Year (YoY) Sales Growth

DAX

YoY Sales Growth =  

VAR PreviousYearSales = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Sales[Date]))  

RETURN (SUM(Sales[Amount]) - PreviousYearSales) / PreviousYearSales  

✅ This formula compares current year sales with last year’s sales.

🔹 Month-over-Month (MoM) Sales Growth

DAX

MoM Sales Growth =  

VAR PreviousMonthSales = CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH(Sales[Date]))  

RETURN (SUM(Sales[Amount]) - PreviousMonthSales) / PreviousMonthSales  

✅ This formula helps track monthly sales trends.

📊 Use Case: A retail company tracks YoY and MoM sales growth to measure performance and adjust marketing strategies.

2️⃣ Running Totals & Cumulative Sales in Power BI

Cumulative totals help track progressive revenue, expenses, or costs over a period.

🔹 Running Total (Cumulative Sales) using DAX

DAX

Cumulative Sales =  

CALCULATE(  

   SUM(Sales[Amount]),  

   FILTER(  

      ALL(Sales[Date]),  

      Sales[Date] <= MAX(Sales[Date])  

   )  

)

✅ This formula calculates cumulative sales from the beginning of time to the current date.

📊 Use Case: A financial analyst tracks cumulative revenue to assess business growth over time.

3️⃣ Dynamic Forecasting with DAX

Forecasting helps businesses predict future sales, revenue, and expenses.

🔹 3-Month Moving Average Forecast

DAX

Moving Average =  

AVERAGEX(  

   DATESINPERIOD(Sales[Date], MAX(Sales[Date]), -3, MONTH),  

   CALCULATE(SUM(Sales[Amount]))  

)

✅ This formula calculates the average sales for the last 3 months to forecast future sales trends.

📊 Use Case: A CFO predicts sales for the next quarter based on past 3-month trends.

4️⃣ Time Intelligence Functions (YTD, MTD, QTD)

Time intelligence functions help analyze financial data across different time periods.

🔹 Year-to-Date (YTD) Sales

DAX

YTD Sales = TOTALYTD(SUM(Sales[Amount]), Sales[Date])  

🔹 Month-to-Date (MTD) Sales

DAX

MTD Sales = TOTALMTD(SUM(Sales[Amount]), Sales[Date])  

🔹 Quarter-to-Date (QTD) Sales

DAX

QTD Sales = TOTALQTD(SUM(Sales[Amount]), Sales[Date])  

✅ These formulas help track performance within a specific financial period.

📊 Use Case: A finance manager tracks YTD revenue to measure annual business performance.

5️⃣ Advanced Financial KPIs using DAX

🔹 Return on Investment (ROI)

DAX

ROI =  

DIVIDE(  

   SUM(Revenue[Profit]),  

   SUM(Investment[Capital]),  

   0  

)

✅ This formula calculates the return on investment for financial decision-making.

🔹 Profit Margin Calculation

DAX

Profit Margin =  

DIVIDE(  

   SUM(Revenue[Profit]),  

   SUM(Revenue[Sales]),  

   0  

)

✅ This formula calculates the percentage of profit in total sales.

🔹 Variance Analysis (Actual vs Budgeted Sales)

DAX

Sales Variance = SUM(Sales[Actual]) - SUM(Sales[Budget])  

• ✅ This formula helps analyze budget deviations.
• 📊 Use Case: A finance team monitors profit margins and ROI to optimize investments.

6️⃣ Power BI Advanced DAX Techniques – Quick Summary

• ✔ YoY and MoM Growth Analysis – Measure sales trends over time
• ✔ Running Totals & Cumulative Sales – Track progressive revenue
• ✔ Forecasting with DAX – Use moving averages for predictions
• ✔ Time Intelligence Functions – Analyze YTD, MTD, QTD trends
• ✔ Financial KPIs – ROI, Profit Margin, Variance Analysis

🚀 Using these advanced DAX formulas, financial analysts can gain deeper insights into business performance.

7️⃣ What’s Next?

📌 Coming up next:

• ✔ How to Create Interactive Power BI Dashboards
• ✔ Power BI Security – Row-Level Security (RLS) Best Practices
• ✔ Power BI for Financial Reporting – Best Practices & Templates

🔔 Stay tuned to Virvijay.com for more Power BI tutorials!

📩 For queries, contact support@virvijay.com

💬 Drop your questions in the comments below!