Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Finishing RETURN portion for calculating XIRR through multiple variable tables

First of all, I found this link quite helpful but did not resolve my issue as I am basically trying to achive something similar:
Solved: XIRR on a dynamic union table - Microsoft Fabric Community

Background:
I am trying to calculate the % difference of IRR based on a time slicer between the original cashflows (baseline) and added cashflows (change) depending on that time slicer.
Therefore, I`m defining tables within a measure to remain dynamic in what rows are selected.
1. Defining a table for baseline cashflow
2. calcualting the IRR  based on 1
3. Defining another table for the change cashflows
4. Union the 1 & 3
5. calculating the IRR based on 4
6. Stuck trying to return the following 


I have built the following measure: 

WAIRR Change = 

var tableWIRR3= SUMMARIZE(tableIRR3,[Asset Scenario ID],[Source Parameter],[IRR base],[total capex base],"WIRR base",[IRR base]*[total capex base])
var tableWIRR2= SUMMARIZE(tableIRR2,[Asset Scenario ID],[Source Parameter],[IRR],[total capex],"WIRR",[IRR]*[total capex])
Return 0 

I need to be returning
(  Sum([WIRR])/Sum([total capex])    -   (Sum([WIRR base])/Sum([total capex base]) )   -1

But cant seem to pull it off with the syntax and I need help checking what syntax to use and that my logic in building this is correct.

I`m also attaching the full code (as a picture, did not want to paste the entire thing in here) of the measure WAIRR Change below as I`m summarizing multiple tables to reach what I have written up.

Any help is much appreciated.


Full code of measure
Need Help

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I am in fact using the XIRR function to calculate IRR for each of my "Asset scenario ID" under each "Source Parameter" at a previous step through this variable: 

      var tableIRR3= SUMMARIZE(tablebase2,[Asset Scenario ID],[Source Parameter],"total capex base",SUMX(tablebase2,[Capex]),"IRR base",XIRR(tablebase2,[Cashflow],[Impacted Date]))

      What I am trying to do is to calculate the weighted IRR (IRR * Capex) for two sub-sets of the data and visualise the change % between them and hence why 
      "
      I need to be returning
      (  Sum([WIRR])/Sum([total capex])    -   (Sum([WIRR base])/Sum([total capex base]) )   -1
      "
      Comparing against tables created using the same exact syntax, I can see that the results are correct but I believe it`s SUMX that is re-evaluating and returning results that are not required. 

      Please do take a look at the screenshot for the full measure, I would really appreciate help in closing this off. 

      Many thanks for your comment.