cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OwenAuger Community Champion
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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




tonyp
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

tonyp
Frequent Visitor

Re: XIRR in differents periods

Hi @OwenAuger 

 

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:

 

Combined Data.PNG

 

Kind regards

 

Tony

OwenAuger Community Champion
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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




tonyp
Frequent Visitor

Re: XIRR in differents periods

This result is the following error:

 

Cashflow Error.PNG

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:

 

Investment IRR Cashflow Error.PNG

 

Data Model.PNG 

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] )
            ) 
OwenAuger Community Champion
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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
tonyp
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.

HennemanTJ
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.

 

Thanks in advance!

OwenAuger Community Champion
Community Champion

Re: XIRR in differents periods

Hi @HennemanTJ 

 

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 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors