Advanced Financial Analysis DAX Formulas in Power BI
Creates complex DAX measures for time intelligence calculations, cost allocation, and profitability analysis by segment in Power BI.
Develop a production-ready set of DAX measures that solve the most common financial BI calculations: YoY, YTD, PMPM, margin calculation by segment, indirect expense allocation, and linear forecasting in Power BI Desktop.
At a glance
Access
Free prompt
Open to copy — no account or payment needed.
Prompt objective
Develop a production-ready set of DAX measures that solve the most common financial BI calculations: YoY, YTD, PMPM, margin calculation by segment, indirect expense allocation, and linear forecasting in Power BI Desktop.
Real use case
A fitness chain with 18 locations across two states, generating $4.2M annually, has a Power BI setup with only basic SUM and COUNT measures. The BI analyst spends 3 days each month in Excel doing YoY variance and contribution margin calculations by location that could be automated.
Customize these fields first
Replace the placeholders with your own context before you run the prompt. That usually improves the first output more than adding more instructions later.
Prompt
Create a complete set of DAX measures for the data model of [COMPANY NAME], operating in the [SECTOR] sector, with the following tables: [FACT_SALES], [DIM_TIME], [DIM_PRODUCT], [DIM_CUSTOMER], [DIM_LOCATION].
**Group 1 — Time Intelligence:**
```dax
// Revenue for Selected Period
Total Revenue = SUMX(FACT_SALES, FACT_SALES[Quantity] * FACT_SALES[UnitPrice])
// Revenue Same Period Last Year (SAMEPERIODLASTYEAR)
Revenue Last Year =
CALCULATE(
[Total Revenue],
SAMEPERIODLASTYEAR(DIM_TIME[Date])
)
// Year-over-Year Variance in %
YoY Variance % =
DIVIDE([Total Revenue] - [Revenue Last Year], [Revenue Last Year], BLANK())
// YTD Revenue (year-to-date cumulative)
Revenue YTD =
CALCULATE([Total Revenue], DATESYTD(DIM_TIME[Date]))
// MTD Revenue (month-to-date cumulative)
Revenue MTD =
CALCULATE([Total Revenue], DATESMTD(DIM_TIME[Date]))
// 3-Month Moving Average
3M Moving Average =
CALCULATEX(
DATESINPERIOD(DIM_TIME[Date], LASTDATE(DIM_TIME[Date]), -3, MONTH),
[Total Revenue]
) / 3
```
**Group 2 — Margin and Profitability:**
```dax
// Cost of Goods Sold
COGS = SUMX(FACT_SALES, FACT_SALES[Quantity] * RELATED(DIM_PRODUCT[UnitCost]))
// Gross Margin in $
Gross Margin = [Total Revenue] - [COGS]
// Gross Margin %
Gross Margin % = DIVIDE([Gross Margin], [Total Revenue], 0)
// Contribution Margin by Segment
CM by Segment =
CALCULATE(
[Gross Margin],
ALLEXCEPT(DIM_PRODUCT, DIM_PRODUCT[Segment])
)
// Product Ranking by Margin (Pareto)
Margin Ranking =
RANKX(ALL(DIM_PRODUCT[Product]), [Gross Margin], , DESC, DENSE)
```
**Group 3 — Customer and Ticket Analysis:**
```dax
// Unique Customers in Period
Active Customers = DISTINCTCOUNT(FACT_SALES[CustomerID])
// Average Ticket
Average Ticket = DIVIDE([Total Revenue], DISTINCTCOUNT(FACT_SALES[OrderID]), 0)
// New Customers (first purchase in period)
New Customers =
CALCULATEX(
FILTER(
VALUES(FACT_SALES[CustomerID]),
CALCULATE(MIN(FACT_SALES[OrderDate])) >= MIN(DIM_TIME[Date])
),
COUNTROWS(VALUES(FACT_SALES[CustomerID]))
)
// Returning Customers
Returning Customers = [Active Customers] - [New Customers]
// Repeat Purchase Rate
Repeat Purchase % = DIVIDE([Returning Customers], [Active Customers], 0)
```
**Group 4 — Targets and Traffic Light Status:**
```dax
// Target Attainment %
Target Attainment % = DIVIDE([Total Revenue], SUM(TARGETS[TargetRevenue]), 0)
// Traffic Light Status (for conditional formatting)
Target Status =
SWITCH(
TRUE(),
[Target Attainment %] >= 1, Open directly in an AI — the text is pre-filled:
How to use this prompt
- 1Replace the key placeholders first: COMPANY NAME, SECTOR, FACT_SALES, DIM_TIME.
- 2Replace any bracketed placeholders like [this] with your own context.
- 3Add extra background information when you want more tailored results.
- 4Combine multiple prompts in one conversation when you need a richer output.
- 5Save your best-performing prompts so they are easy to reuse later.
Next best step
Open the guide first, then branch only if you still need more.
A guide for technical builders choosing between prompts, coding workflows, and agent-based implementation.
If this prompt is close but not quite right, generate variants next. If the job is recurring, move into the course library after the guide.
Related prompts
View allPredictive Analysis with Python: Regression and Demand Forecasting
Builds a demand forecasting model using scikit-learn and Prophet, with cross-validation and API deployment for daily business use.
Best for
Create a complete Python predictive analytics pipeline — from data preparation to generating forecasts consumable by the operations team — using linear regression, decision trees, and Facebook Prophet for time series analysis.
Multichannel Marketing Data Correlation with Revenue Attribution
Correlation analysis between marketing channels (Google, Meta, email, organic) and revenue, with a multi-touch attribution model for budget optimization.
Best for
Identify which marketing channels have the highest correlation with conversions and revenue, build a multi-touch attribution model, and distribute media budget based on actual performance data—replacing last-click attribution.
Complete Cohort Analysis: Retention, Revenue, and Product Behavior
Cohort analysis framework for digital products covering user retention, revenue per cohort, feature adoption, and high-value user profile identification.
Best for
Implement cohort analysis across three dimensions—usage retention, cumulative revenue, and feature adoption—to identify which customer cohorts are most valuable, what differentiates users who stay from those who churn, and where to focus product and marketing efforts.
LTV Calculation for SaaS: Models, Segmentation, and Growth Impact
Complete framework for calculating SaaS Lifetime Value with segmented churn by plan, inflation correction, and strategic use of LTV for CAC decisions, pricing, and product cohorts.
Best for
Develop a robust LTV model accounting for real market conditions—interest rates, inflation, segmented churn, and currency effects—enabling the growth team to make acquisition budget, pricing, and product decisions based on actual customer value per cohort.
Explore other prompt categories
Move sideways into adjacent libraries when the current category is not the full answer.
Every prompt here is free. The course teaches the thinking behind them.
Copy as many prompts as you like. When you want to move from single prompts to a repeatable AI workflow, Learn AI in 30 Days walks through it, one day at a time.
Buy the course once ($15/$20 by length), or go all-access for $10/mo with a verifiable certificate.