The XIRR function is not working

Aymane Thanks for that 🙂

First of all, can you test this:

1. Create a simple table visual containing Dimension_Date[Today] and XIRR_Calc2.
2. Do you see the cashflow values that you are expecting to feed into the IRR calculation?

If so, you could rewrite the XIRR measure as:

XIRR =
XIRR (
    VALUES ( Dimension_Date[Today] ), -- OR Dimension_Date
    [XIRR_calc2],
    Dimension_Date[Today]
)

If not, the logic may need to be rewritten in some way, either by modifying XIRR_Calc2 so that it returns the required cashflow when grouped by date, or adding some logic to the XIRR measure.

There are a couple of points to consider here:

1. Iterating over a fact table with XIRR will likely give an unexpected result, due to the risk of duplicated rows. For this reason (and performance reasons) it's best to iterate over a dimension table (or dimension column).
2. We need to ensure that XIRR_Calc2 returns the expected values for each row of the table provided in the first argument.  Given that there is some complexity, with vMin and vMax being evaluated within the measure, there might be some tweaks required.

To explain further:

With your current XIRR measure, the XIRR_Calc2 measure is being evaluated in the row context of every row of Fact_TransactionBuckets. Due to context transition (since a measure is being evaluated), each row of Fact_TransactionBuckets is converted into an equivalent filter context, and if there happen to be any duplicated rows, things could go awry (good article on context transition here).

Regards,

Owen