Forum Discussion
The XIRR function is not working
Aymane Thanks for that 🙂
First of all, can you test this:
- Create a simple table visual containing Dimension_Date[Today] and XIRR_Calc2.
- 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:
- 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).
- 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
Aymane Thanks for that 🙂
First of all, can you test this:
- Create a simple table visual containing Dimension_Date[Today] and XIRR_Calc2.
- 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:
- 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).
- 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
Hi OwenAuger , Thank you again for your solution.
I have another issue regarding the same problem, after calculating the XIRR, I need to create a visualization table where XIRR is filtered by another column(AssetClass) in another table (Dimension_Asset).
Usually, all the calculations I've made have been done on the Fact_TransactionBuckets which has an AssetKey that connects it with the Dimension_Asset, however, since the Dimension_date Table (Where the XIRR was calculated) doesn't have a direct relationship with Dimension Asset, the XIRR can not be filtered by the column AssetClass.
Can you please suggest a solution that doesn't involve adding or changing anything in the model?
You're welcome Aymane 🙂
If your data model has been set up the way you've described, with Fact_TransactionBuckets related to both Dimension_Asset and Dimension_Date, then filters on either of those dimension tables should filter Fact_TransactionBuckets and the XIRR measure should respond accordingly.
To help me understand the issue, could you post a sanitised version of the PBIX with dummy data?
I wasn's sure what you meant when you say the calculation was does "in" the Dimension_date or Fact_TransactionBuckets tables? The measure does reference those tables, but it should return the same result regardless of its home table.
Also just want to check I've understood you correctly and you have created a measure rather than a calculated column.
Regards,
Owen
Hi OwenAuger ,
I have created a dummy data model as follows:
For the XIRR, I created a measure in the Fact_TransactionBuckets; when I put the measure into a Table, it works fine, however, when I add AssetClass to it, the following error message is prompted:
The same doesn't happen when I use other measures added to Fact_TransactionBuckets instead of XIRR, example:
Thanks Aymane
Having re-looked at the XIRR_calc2 measure, as it in part references min/max dates, from Dimension_Date, one solution might be to derive a date filter from Fact_TransactionBuckets and apply that to the overall calculation.
Something like:
XIRR = CALCULATE ( XIRR ( VALUES ( Dimension_Date[Today] ), -- OR Dimension_Date [XIRR_calc2], Dimension_Date[Today] ), SUMMARIZE ( Fact_TransactionBuckets, Dimension_Date[Today] ) )If you could share a PBIX with dummy data and expected result for a given Asset filter, I may be able to give a more watertight answer.
Regards,
Owen