Daily DAX : Day 440 XIRR

Power BI DAX Function: XIRR

The XIRR function in DAX (Data Analysis Expressions) calculates the Internal Rate of Return (IRR) for a series of cash flows that occur at irregular intervals. It is particularly useful for financial analysis where payments or receipts do not happen periodically.

What is XIRR?

XIRR stands for eXtended Internal Rate of Return. It finds the interest rate that makes the net present value (NPV) of the cash flows equal to zero, accounting for the exact timing of each cash flow using specific dates.

  • Negative values typically represent outflows (e.g., investments or costs).
  • Positive values represent inflows (e.g., returns or income).
  • There must be at least one positive and one negative cash flow for the calculation to work.

Syntax

XIRR(, ,  [, ] [, ])

Parameters

Parameter Description
A table expression containing the cash flows and dates (usually a reference to a table).
A column in the table containing the cash flow amounts (negative for outflows, positive for inflows).
A column in the table containing the corresponding dates for each cash flow.
(optional) A decimal number guessing the expected IRR (default is 0.1 or 10%). Helps convergence in complex cases.
(optional) Value to return if no valid IRR is found (e.g., BLANK() or 0).

Example

Assume a table named CashFlows with columns: [Date] and [Amount].

XIRR_Measure = XIRR(CashFlows, CashFlows[Amount], CashFlows[Date])

Sample data:

Date Amount
2024-01-01 -10000
2024-06-15 5000
2025-12-27 8000

This might return an IRR of approximately 0.35 (35%), depending on exact calculations.

Use Cases

  • Investment Performance: Calculate returns on investments like mutual funds, stocks, or private equity with irregular contributions/redemptions.
  • Project Evaluation: Assess profitability of projects with non-uniform cash flows (e.g., construction projects).
  • Loan Analysis: Determine effective interest rates for loans with irregular payments.
  • Portfolio Reporting: In Power BI dashboards, show annualized returns for financial portfolios.

Notes

  • Avoid zero values in cash flows as they can cause errors or don't affect results.
  • Dates must be in ascending order internally (DAX handles sorting).
  • If convergence fails, try providing a or handle with .

Comments

Post a Comment

Popular posts from this blog

Daily DAX : Day 131 SELECTEDMEASURE

 The SELECTEDMEASURE function in Power BI's DAX (Data Analysis Expressions) language is a powerful tool used primarily within calculation groups to dynamically reference the measure currently being evaluated in a report. It’s part of the calculation group feature introduced to streamline complex reporting scenarios and improve maintainability by reducing the need to duplicate measure logic. What is SELECTEDMEASURE? SELECTEDMEASURE is a DAX function that returns a reference to the measure that is currently active or "selected" in the context of a calculation group. It doesn’t take any arguments and is typically used in the expression of a calculation item within a calculation group. This allows you to write generic logic that can dynamically apply to whichever measure is being evaluated in a visual, without hardcoding specific measure names. Syntax SELECTEDMEASURE()     Returns: A reference to the measure currently being evaluated in the report context. Use Case SELECTEDME...
Read more

Daily DAX : Day 446 INFO.CSDLMETADATA

Power BI DAX Function: INFO.CSDLMETADATA INFO.CSDLMETADATA` is a specialized DAX function introduced as part of a broader set of "INFO" functions (like `INFO.TABLES` or `INFO.MEASURES`) used for **model introspection**. It allows you to programmatically access the underlying structure of your Power BI semantic model. What does it do? The function returns a table with a single column containing the Conceptual Schema Definition Language (CSDL)  metadata of your model in XML format . In plain English, it exports a highly technical "blueprint" of your entire data model—including tables, columns, relationships, and measures—as a structured XML string. It is essentially the DAX equivalent of the `DISCOVER_CSDL_METADATA` Dynamic Management View (DMV) used in Analysis Services. --- Syntax and Output The syntax is straightforward as it requires no arguments: ```dax EVALUATE INFO.CSDLMETADATA() ``` Return Value : A table with one column and one row. Content : A long string ...
Read more

Daily DAX : Day 453 ENDOFWEEK

Power BI DAX Function: ENDOFWEEK Description The ENDOFWEEK function is a Time Intelligence function in DAX (Data Analysis Expressions) used in Power BI. It returns the date that represents the end of the week containing a specified date (typically Saturday, as weeks are considered to start on Sunday by default in DAX time intelligence). Syntax ENDOFWEEK( [, ]) : A column reference (usually from a Date table) or a date expression. Required. : Optional. Specifies the week-ending convention. Default is Saturday if omitted. Examples 1. Basic usage (default: week ends on Saturday): End of Week = ENDOFWEEK('Date'[Date]) For a date like January 8, 2026 (Thursday), this would return January 10, 2026 (Saturday). 2. In a measure for cumulative sales up to end of week: Total Sales to End of Week = CALCULATE( SUM(Sales[Amount]), DATESBETWEEN('Date'[Date], ST...
Read more