Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello everyone,
I would like to create a formula that can calculate the IRR at different periods, I would like to put one filter on the dashboard and when I change the date I also change the value of the IRR. The big problem is that I have tree categories A, B and C, and when I'm working with the last 5 years for example, I need to have the first negative A value and just the last A value as positives.
I would like to replace it as a measure, as I would like to have a filter on the panel to change the date.
Date | Type | Value | 1 Example | 2 Example |
1/1/2016 | A | 35000 | ||
1/1/2016 | A | 300 | ||
6/1/2016 | A | 30000 | -30000 | |
7/1/2016 | A | 36000 | ||
10/1/2016 | B | 100 | 100 | |
11/1/2016 | C | 230 | 230 | |
11/1/2016 | C | 22 | 22 | |
1/1/2017 | A | 35000 | -35000 | |
5/1/2017 | C | 1000 | 1000 | 1000 |
6/1/2017 | A | 500 | 500 | 500 |
10/1/2017 | B | 300 | 300 | |
10/1/2017 | A | 34000 | ||
1/1/2018 | B | 4000 | 4000 | |
1/1/2018 | A | 7000 | 7000 | |
5/1/2018 | B | 7000 | ||
10/1/2018 | C | 4500 |
Solved! Go to Solution.
Hi again @juliliscarmo
I have created a sample pbix with an IRR measure hopefully close to meeting your needs.
My measure likes this (with a few comments):
IRR = VAR First_A_Date = CALCULATE( MIN ( Cashflow[Date] ), Cashflow[Type] = "A" ) VAR Last_A_Date = CALCULATE( MAX ( Cashflow[Date] ), Cashflow[Type] = "A" ) VAR CashflowTable = ADDCOLUMNS ( SUMMARIZE ( Cashflow, Cashflow[Date], Cashflow[Type] ), "Cashflow", -- Aggregate values at a Date & Type level - correct? -- For example, 2 values on 1/1/2016 will be aggregated to 35300. -- If this is not correct, we may need to add an index to distinguish -- values of same type on same date VAR OriginalValue = CALCULATE ( SUM ( Cashflow[Value] ) ) VAR CashflowMultiplier = SWITCH ( TRUE (), Cashflow[Type] = "A" && Cashflow[Date] = First_A_Date, -1, --First Type A is negated Cashflow[Type] = "A" && Cashflow[Date] <> Last_A_Date, 0, --Other Type A is ignored if not last 1 -- All the rest are positive (this includes the final Type A) ) RETURN OriginalValue * CashflowMultiplier ) RETURN XIRR ( CashflowTable, [Cashflow], Cashflow[Date] )
Within the measure, I have aggregated Values at the Type/Date level. However, I notice in your sample data that there are cases of multiple values of the same Type on the same Date. If we need to keep those separate, then we may need some sort of index column.
Also, after re-reading your original post, I realised that the last Type A value needs to be positive.
So the logic I have used is:
If the logic needs to be modified, hopefully it is not too hard to change the above.
Best regards,
Owen
Unable to access the data model can you please share the link to data model .
You can certainly do this. It's a matter of filtering your cashflow table appropriately, negating the "A" cashflow, then passing this to the XIRR function.
Couple of questions before writing a measure:
Regards,
Owen
Hi @OwenAuger,
Thank you for your attention.
1. Yes, absolutely!
2. Yes I would like to select a range of dates.
3. No.
Best Regards
Juli
Hi again @juliliscarmo
I have created a sample pbix with an IRR measure hopefully close to meeting your needs.
My measure likes this (with a few comments):
IRR = VAR First_A_Date = CALCULATE( MIN ( Cashflow[Date] ), Cashflow[Type] = "A" ) VAR Last_A_Date = CALCULATE( MAX ( Cashflow[Date] ), Cashflow[Type] = "A" ) VAR CashflowTable = ADDCOLUMNS ( SUMMARIZE ( Cashflow, Cashflow[Date], Cashflow[Type] ), "Cashflow", -- Aggregate values at a Date & Type level - correct? -- For example, 2 values on 1/1/2016 will be aggregated to 35300. -- If this is not correct, we may need to add an index to distinguish -- values of same type on same date VAR OriginalValue = CALCULATE ( SUM ( Cashflow[Value] ) ) VAR CashflowMultiplier = SWITCH ( TRUE (), Cashflow[Type] = "A" && Cashflow[Date] = First_A_Date, -1, --First Type A is negated Cashflow[Type] = "A" && Cashflow[Date] <> Last_A_Date, 0, --Other Type A is ignored if not last 1 -- All the rest are positive (this includes the final Type A) ) RETURN OriginalValue * CashflowMultiplier ) RETURN XIRR ( CashflowTable, [Cashflow], Cashflow[Date] )
Within the measure, I have aggregated Values at the Type/Date level. However, I notice in your sample data that there are cases of multiple values of the same Type on the same Date. If we need to keep those separate, then we may need some sort of index column.
Also, after re-reading your original post, I realised that the last Type A value needs to be positive.
So the logic I have used is:
If the logic needs to be modified, hopefully it is not too hard to change the above.
Best regards,
Owen
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!
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
Hi @OwenAuger
I would like to know if is possible to calculate the IRR, not XIRR, with the same situation.
Thank you!
Juli
Hi again @juliliscarmo
Yes you certainly can replicate the behaviour of Excel's IRR function.
Basically you need to construct a cashfow table with dates that are at 365 day intervals.
(You can test this in Excel - if you provide XIRR with dates that are at 365 day intervals, you get the same result as the IRR function)
So instead of the variable CashflowTable which I created in the original measure which brings through the original Cashflow[Date] values, you would need a similar table which has dates at 365 day intervals.
It's probably going to be similar to the original formula but with that CashflowTable defined differently. Could you post an example of which values would feed into the IRR calc and expected output?
Cheers
Owen
Hi @OwenAuger
Thanks this is really very useful indeed and I have adapted your code to my own table and needs (see table & code snippet below). I have one further thing I am trying to achieve which is if the user selects a date for which there is no appropriate cashflow the XIRR function errors.
By that I mean you are only guaranteed a result if the date they pick has a valuation on it (type = V and is a positive number). I would like the user to be able to pick any date and in the case of my example table below if they picked 31/08/2015, for example, I could use the last unrealised valuation (i.e. 30/06/2015 of 4,100,000.00) and add the purchase of 30/08/2015 (i.e. -1,060,000.00 N.B. this needs to be made positive before adding to the valuation) to calculate a notional terminal value of 5,160,000.00 to feed the XIRR function as my last cashflow. Then all I need to do is ignore any preceding valuations in the flows I feed to the XIRR function and just include Type =P.
If there were no valuations at all in the date period then I would use all preceding purchases (Type = P) to calculate a terminal value (as you can see in my data I have explicit valuation transactions at the end of each quarter but if this works in DAX I no longer have to rely on there being one as I can calculate my own terminal value on each date the user selects by either looking at the last valuation and adding any purchases and taking out any sales or, assuming no valuations, adding up all of the purchases & sales).
I have done this in SQL many times, but I am new to DAX.
My table is something like this:
Cashflows(Firm, Type, Document Date, Description, Cashflows)
ABC Company | P | 27/04/2015 | Purchase | - 3,000,000.00 |
ABC Company | P | 27/05/2015 | Direct Purchase | - 200,000.00 |
ABC Company | P | 27/05/2015 | Purchase | - 100,000.00 |
ABC Company | P | 27/05/2015 | Direct Purchase | - 200,000.00 |
ABC Company | P | 30/06/2015 | Direct Purchase | - 500,000.00 |
ABC Company | V | 30/06/2015 | Valuation | 4,100,000.00 |
ABC Company | P | 30/08/2015 | Direct Purchase | - 1,060,000.00 |
ABC Company | P | 01/09/2015 | Buy of ABC common | - 200,000.00 |
ABC Company | V | 30/09/2015 | Valuation | 5,360,000.00 |
ABC Company | P | 20/10/2015 | Direct Purchase | - 1,000,000.00 |
ABC Company | P | 22/10/2015 | Direct Purchase | - 100,000.00 |
ABC Company | P | 10/11/2015 | Purchase | - 10,000.00 |
ABC Company | P | 15/12/2015 | Direct Purchase | - 400,000.00 |
ABC Company | P | 16/12/2015 | Purchase | - 1,000,000.00 |
ABC Company | V | 31/12/2015 | Valuation | 10,000,000.00 |
Your modified code which works perfectly as long as you pick a quarter end date in the date filter:
Hi @Anonymous
I am sure what you have described is possible - it's just a matter of correctly constructing the TransactionTable variable using DAX.
Just summarising my reading of your post, with one question in bold:
Let me know if I've got that right and then I can help with the DAX.
It might help if you could provide an example of the resulting dates/cashflows you want to feed into XIRR for both scenarios (with and without V) just to make sure.
Regards,
Owen
Hi @OwenAuger
Thank you for your prompt reply.
My responses as follows:
1.1. The date assigned would be that used in the date filter the user selects. In your example it was based on an auto created table Date ( using =CALENDARAUTO() ) which is linked in my example to Document Date in the Transactions table (see Scenario 2 below). But perhaps I should be using a Parameter instead?
2. As in 1.1. if there are no valautions simply sum all transactions of Type P to create a valuation at cost and this would also take as its date the date selected by the user (see Scenario 1 below).
Scenario 1 - "Date" selected 31/05/2015
Firm | Type | Document Date | Description | Cashflows |
ABC Company | P | 27/04/2015 | Purchase | - 3,000,000.00 |
ABC Company | P | 27/05/2015 | Direct Purchase | - 200,000.00 |
ABC Company | P | 27/05/2015 | Purchase Amadeus Test | - 100,000.00 |
ABC Company | P | 27/05/2015 | Direct Purchase | - 200,000.00 |
ABC Company | 31/05/2015 | ***Calculated Valuation Row*** | 3,500,000.00 |
XIRR = 0.00%
Sceanrio 2 - "Date" selected 31/08/2015
Firm | Type | Document Date | Description | Cashflows |
ABC Company | P | 27/04/2015 | Purchase | - 3,000,000.00 |
ABC Company | P | 27/05/2015 | Direct Purchase | - 200,000.00 |
ABC Company | P | 27/05/2015 | Purchase Amadeus Test | - 100,000.00 |
ABC Company | P | 27/05/2015 | Direct Purchase | - 200,000.00 |
ABC Company | P | 30/06/2015 | Direct Purchase | - 500,000.00 |
ABC Company | V | 30/06/2015 | Valuation | 4,100,000.00 |
ABC Company | P | 30/08/2015 | Direct Purchase | - 1,060,000.00 |
ABC Company | 31/08/2015 | ***Calculated Valuation Row*** | 5,160,000.00 |
XIRR= 8.19%
I hope this make sense but if not let me know?
Kind regards
Tony
Hi again Tony @Anonymous ,
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:
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:
Other notes:
Hopefully that works correctly. Please test! 🙂
Regards,
Owen
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:
Kind regards
Tony
Hi @Anonymous
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
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] )
)
First time 👍 ! I think have some small issues to reconcile in the data but the calculation looks perfect.
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |