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 without upgrading.
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].\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Group 1 — Time Intelligence:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`dax\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// Revenue for Selected Period\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nTotal Revenue = SUMX(FACT_SALES, FACT_SALES[Quantity] * FACT_SALES[UnitPrice])\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// Revenue Same Period Last Year (SAMEPERIODLASTYEAR)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nRevenue Last Year =\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nCALCULATE(\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n [Total Revenue],\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n SAMEPERIODLASTYEAR(DIM_TIME[Date])\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// Year-over-Year Variance in %\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nYoY Variance % =\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nDIVIDE([Total Revenue] - [Revenue Last Year], [Revenue Last Year], BLANK())\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// YTD Revenue (year-to-date cumulative)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nRevenue YTD =\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nCALCULATE([Total Revenue], DATESYTD(DIM_TIME[Date]))\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// MTD Revenue (month-to-date cumulative)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nRevenue MTD =\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nCALCULATE([Total Revenue], DATESMTD(DIM_TIME[Date]))\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// 3-Month Moving Average\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n3M Moving Average =\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nCALCULATEX(\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n DATESINPERIOD(DIM_TIME[Date], LASTDATE(DIM_TIME[Date]), -3, MONTH),\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n [Total Revenue]\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n) / 3\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Group 2 — Margin and Profitability:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`dax\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// Cost of Goods Sold\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nCOGS = SUMX(FACT_SALES, FACT_SALES[Quantity] * RELATED(DIM_PRODUCT[UnitCost]))\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// Gross Margin in $ \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nGross Margin = [Total Revenue] - [COGS]\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// Gross Margin %\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nGross Margin % = DIVIDE([Gross Margin], [Total Revenue], 0)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// Contribution Margin by Segment\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nCM by Segment =\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nCALCULATE(\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n [Gross Margin],\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n ALLEXCEPT(DIM_PRODUCT, DIM_PRODUCT[Segment])\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// Product Ranking by Margin (Pareto)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nMargin Ranking =\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nRANKX(ALL(DIM_PRODUCT[Product]), [Gross Margin], , DESC, DENSE)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Group 3 — Customer and Ticket Analysis:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`dax\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// Unique Customers in Period\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nActive Customers = DISTINCTCOUNT(FACT_SALES[CustomerID])\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// Average Ticket\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nAverage Ticket = DIVIDE([Total Revenue], DISTINCTCOUNT(FACT_SALES[OrderID]), 0)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// New Customers (first purchase in period)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nNew Customers =\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nCALCULATEX(\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n FILTER(\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n VALUES(FACT_SALES[CustomerID]),\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n CALCULATE(MIN(FACT_SALES[OrderDate])) >= MIN(DIM_TIME[Date])\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n ),\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n COUNTROWS(VALUES(FACT_SALES[CustomerID]))\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// Returning Customers\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nReturning Customers = [Active Customers] - [New Customers]\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// Repeat Purchase Rate\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nRepeat Purchase % = DIVIDE([Returning Customers], [Active Customers], 0)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Group 4 — Targets and Traffic Light Status:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`dax\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// Target Attainment %\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nTarget Attainment % = DIVIDE([Total Revenue], SUM(TARGETS[TargetRevenue]), 0)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n// Traffic Light Status (for conditional formatting)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nTarget Status =\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nSWITCH(\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n TRUE(),\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n [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.
Free browsing stays open. Premium prompts unlock the reusable workflow layer.
Use the guides and role paths to validate the job first. Upgrade when you want the full prompt text, editable premium prompts, and the surrounding course paths in one place.
Free access
- Browse guides, role paths, and category pages.
- Preview prompts before you decide to upgrade.
- Find the right starting point without friction.
Membership access
- Unlock premium prompts and the full copy text.
- See more workflow paths and course connections.
- Keep the reusable templates in one place.
