cancel
Showing results for
Did you mean:
Community Champion

## Re: XIRR in differents periods

Hi again Tony @tonyp ,

Thanks - great explanation!

I have uploaded a dummy Power BI model testing my measure below.

After looking through your examples and replicating the calculations myself, I think the logic boils down to:

1. Always include all P rows as-is
2. Construct one additional "Valuation row" with value equal to the value from the last V row plus values from any subsequent P rows (negated), assigned to the max filtered date. In the absence of any V row, all P rows are included.
3. Take the union of the tables from 1 & 2, and calculate XIRR over that table.

Based on this, I re-wrote the measure from scratch in a way that I think makes more sense.

```IRR =
VAR Last_V_Date =
CALCULATE ( MAX ( Transactions[Document Date] ), Transactions[Type] = "V" )
VAR MaxDateSelected =
MAX ( 'Date'[Date] )
VAR PurchaseRows =
CALCULATETABLE (
SELECTCOLUMNS (
Transactions,
"Date", Transactions[Document Date],
"Cashflow", Transactions[Cashflows]
),
Transactions[Type] = "P"
)
VAR Valuation_Purchases_Component =
CALCULATE (
SUM ( Transactions[Cashflows] ),
Transactions[Type] = "P",
KEEPFILTERS ( 'Date'[Date] > Last_V_Date || ISBLANK ( Last_V_Date ) )
) * -1
VAR Valuation_Valuation_Component =
CALCULATE (
SUM ( Transactions[Cashflows] ),
'Date'[Date] = Last_V_Date,
Transactions[Type] = "V"
)
VAR Valuation = Valuation_Purchases_Component + Valuation_Valuation_Component
VAR ValuationRow =
{ ( MaxDateSelected, Valuation ) }
VAR TransactionTable =
UNION ( PurchaseRows, ValuationRow )
RETURN
XIRR ( TransactionTable, [Cashflow], [Date] )```

To summarise the important variables:

• PurchaseRows is includes Date/Cashflows from all P rows
• Valuation_Purchases_Component is the total value of P rows after the last V Date.
• Valuation_Valuation_Component is just the value of the V row on the last V Date
• Valuation is the sum of these
• ValutionRow is the last filtered date and Valuation put together in a single-row table
• TransactionTable is final table combining PurchaseRows and ValuationRow, and XIRR is calculated over this table

Other notes:

• I ditched the SUMMARIZE as it wasn't really needed and XIRR doesn't mind if we have repeated dates.
• We could also make the measure return 0 by default when there are no V rows

Hopefully that works correctly. Please test! 🙂

Regards,

Owen

Proud to be a Datanaut!

Frequent Visitor

## Re: XIRR in differents periods

Hi Owen

Once again thank you for looking at this for me.  I will go away and test on a broader data set and let you know. But on initial limited testing this looks perfect!

Kind regards

Tony

Highlighted
Frequent Visitor

## Re: XIRR in differents periods

I hope you are well?  I am migrating the IRR code you orginally devised to a new tabular cube with fact & dimension tables.  I have hit a snag in that the table with the cashflows (vwv_FactGL) does not contain a date any longer but a link to a Date table instead (FYI this is a Live connection so I can only create Measures).  This means my code now fails at the SELECTCOLUMNS statement (shown in red):

IRR =
VAR MaxDateSelected = MAX ( 'Date'[Date] )
VAR PurchaseRows =
SELECTCOLUMNS (
vwv_FactGL,
"Date", Date[Date],
"Cashflow", vwv_FactGL[Cashflows]
)
VAR Valuation_Valuation_Component =
CALCULATE (
SUM ( 'vwv_FactGL'[Investment Balance] ),
'Date'[Date] <= MaxDateSelected
)
VAR Valuation = Valuation_Valuation_Component
VAR ValuationRow =
{ ( MaxDateSelected, Valuation ) }
VAR TransactionTable =
UNION ( PurchaseRows, ValuationRow )
RETURN
IF ( ISERROR (
XIRR ( TransactionTable, [Cashflow], [Date] )
)
,BLANK()
,XIRR ( TransactionTable, [Cashflow], [Date] )
)

My question is how to combine data from the two tables as if it was one all in one in the DAX?

The two tables are Date[Date] and vv_FactGL[Cashflows] & [Investment Balance] (see below) and I am trying to create the IRR measure in the vwv_FactGL table:

Kind regards

Tony

Community Champion

## Re: XIRR in differents periods

Hi @tonyp

I'm fine thanks, hope you're well 🙂

The simplest fix I can think of is to change

``````SELECTCOLUMNS (
vwv_FactGL,
"Date", Date[Date],
"Cashflow", vwv_FactGL[Cashflows]
)``````

to

``````SELECTCOLUMNS (
SUMMARIZE ( vwv_FactGL, 'Date'[Date] ),
"Date", 'Date'[Date],
"Cashflow", CALCULATE ( SUM ( vwv_FactGL[Cashflows] ) )
)``````

This will effectively group by Dates that exist in vwv_FactGL and sum Cashflows on those dates. For the IRR calculation, it makes no difference whether dates are distinct or not.

Does this work in your current model?

Kind regards,

Owen

Proud to be a Datanaut!

Frequent Visitor

## Re: XIRR in differents periods

This result is the following error:

I should have said the field Cashflows is itself a measure:

Cashflows = SUM('vwv_FactGL'[Investment IRR Cashflows]) * -1

It is needed to invert the sign of the actual cashflow field Investment IRR Cashflows.  So I changed your code to reference the actual field (ignoring for now the sign being wrong) and got this instead:

``````IRR =
VAR MaxDateSelected = MAX ( 'Date'[Date] )
VAR PurchaseRows =
SELECTCOLUMNS (
SUMMARIZE (
'vwv_FactGL',
'Date'[Date]
),
"Date", 'Date'[Date],
"Cashflow",
CALCULATE ( SUM ( 'vwv_FactGL'[Investment IRR Cashflows] )
)
)
VAR Valuation_Valuation_Component =
CALCULATE (
SUM ( 'vwv_FactGL'[Investment Balance] ),
'Date'[Date] <= MaxDateSelected
)
VAR Valuation = Valuation_Valuation_Component
VAR ValuationRow =
{ ( MaxDateSelected, Valuation ) }
VAR TransactionTable =
UNION ( PurchaseRows, ValuationRow )
RETURN
IF ( ISERROR (
XIRR ( TransactionTable, [Cashflow], [Date] )
)
,BLANK()
,XIRR ( TransactionTable, [Cashflow], [Date] )
) ``````
Community Champion

## Re: XIRR in differents periods

Hi again Tony

I'm not certain, but there may be an issue with the {} table constructor in your Analysis Services Tabular version.

Also, we can use the Cashflow measure within the definition of PurchaseRows.

You could use the ROW function instead in this case. Also since Valuation is set equal to Valuation_Valuation_Component, we can eliminate one of those variables.

Does the below code work?

``````IRR =
VAR MaxDateSelected =
MAX ( 'Date'[Date] )
VAR PurchaseRows =
SELECTCOLUMNS (
SUMMARIZE ( 'vwv_FactGL', 'Date'[Date] ),
"Date", 'Date'[Date],
"Cashflow", [Cashflow]
)
VAR Valuation =
CALCULATE (
SUM ( 'vwv_FactGL'[Investment Balance] ),
'Date'[Date] <= MaxDateSelected
)
VAR ValuationRow =
ROW ( "Date", MaxDateSelected, "Cashflow", Valuation )
VAR TransactionTable =
UNION ( PurchaseRows, ValuationRow )
RETURN
IF (
ISERROR ( XIRR ( TransactionTable, [Cashflow], [Date] ) ),
BLANK (),
XIRR ( TransactionTable, [Cashflow], [Date] )
)``````

Regards,

Owen

Proud to be a Datanaut!

Frequent Visitor

## Re: XIRR in differents periods

First time 👍 !  I think have some small issues to reconcile in the data but the calculation looks perfect.

New Member

## Re: XIRR in differents periods

Hey @OwenAuger - how would you tweak this initially for filtering by a part number first.  I have a very similar problem where I'm needing to get IRR for individual part numbers and then provide an IRR by period and your advice is the closest that I've found to what I think I need.

Basically the IRR calc fails when I look at everything because there are multiples of the same date with different outflows and inflows.

So I think I need to filter it down to look by part # first then run IRR on those.

Community Champion

## Re: XIRR in differents periods

Generally speaking, if you need to perform any IRR calculation with a particular filter applied (such as an individual part number), you should be able to apply the relevant filter by slicer or otherwise and get a meaningful result.

In your case, if you include a "single select" slicer on part number, does your IRR measure give a meaningful result?

You can also include a safeguard in the measure itself, to blank out the result if more than one part number is filtered, looking something like:

``````IRR measure with safeguard =
IF (
HASONEVALUE ( YourTable[Part Number] ),
[IRR original measure]
)``````

Please post back with more details if you need some more help.

Regards,

Owen

Proud to be a Datanaut!

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors