How to calculate IRR using DAX

sanjaymithran — Thu, 10 Aug 2023 05:29:28 GMT

Hi All,

XIRR=XIRR(Data,Data[Cash Flow],Data[DATE]) working fine we have function getting same as mentioned below.

I tried as explained in that video getting 0% but in excel 0.45%

IRR =
XIRR (
ADDCOLUMNS (
'Data',
"Date365", CALCULATE (
COUNTROWS ( 'Data' ),
'Data'[DATE] < EARLIER ( Data[DATE]),
ALL ( 'Data' )
)
* 365
+ CALCULATE ( MIN ( Data[DATE] ), ALL ( 'Data') )
),
Data[Cash Flow],
[Date365]
) )

SPONSOR	DATE	Year	Month	Cash Flow	IRR	XIRR
Sponsor1	1/1/2000 0:00	0	0	-509060.5	0.45%	0.80%
Sponsor1	1/1/2023 0:00	1	1	9789.92
Sponsor1	2/1/2023 0:00	1	2	9573.44
Sponsor1	3/1/2023 0:00	1	3	9361.79
Sponsor1	4/1/2023 0:00	1	4	9154.88
Sponsor1	5/1/2023 0:00	1	5	8952.62
Sponsor1	6/1/2023 0:00	1	6	8754.88
Sponsor1	7/1/2023 0:00	1	7	8561.59
Sponsor1	8/1/2023 0:00	1	8	8372.61
Sponsor1	9/1/2023 0:00	1	9	8187.85
Sponsor1	10/1/2023 0:00	1	10	8007.23
Sponsor1	11/1/2023 0:00	1	11	7830.64
Sponsor1	12/1/2023 0:00	1	12	7658.02
Sponsor1	1/1/2024 0:00	2	1	7489.25
Sponsor1	2/1/2024 0:00	2	2	7391.31
Sponsor1	3/1/2024 0:00	2	3	7294.71
Sponsor1	4/1/2024 0:00	2	4	7199.45
Sponsor1	5/1/2024 0:00	2	5	7105.49
Sponsor1	6/1/2024 0:00	2	6	7012.8
Sponsor1	7/1/2024 0:00	2	7	6921.4
Sponsor1	8/1/2024 0:00	2	8	6831.23
Sponsor1	9/1/2024 0:00	2	9	6742.31
Sponsor1	10/1/2024 0:00	2	10	6654.57
Sponsor1	11/1/2024 0:00	2	11	6568.05
Sponsor1	12/1/2024 0:00	2	12	6482.69
Sponsor1	1/1/2025 0:00	3	1	6398.52
Sponsor1	2/1/2025 0:00	3	2	6348.35
Sponsor1	3/1/2025 0:00	3	3	6298.69
Sponsor1	4/1/2025 0:00	3	4	6249.54
Sponsor1	5/1/2025 0:00	3	5	6200.88
Sponsor1	6/1/2025 0:00	3	6	6152.69
Sponsor1	7/1/2025 0:00	3	7	6104.98
Sponsor1	8/1/2025 0:00	3	8	6057.76
Sponsor1	9/1/2025 0:00	3	9	6010.98
Sponsor1	10/1/2025 0:00	3	10	5964.67
Sponsor1	11/1/2025 0:00	3	11	5918.82
Sponsor1	12/1/2025 0:00	3	12	5873.42
Sponsor1	1/1/2026 0:00	4	1	5828.43
Sponsor1	2/1/2026 0:00	4	2	5778.2
Sponsor1	3/1/2026 0:00	4	3	5728.42
Sponsor1	4/1/2026 0:00	4	4	5679.05
Sponsor1	5/1/2026 0:00	4	5	5630.16
Sponsor1	6/1/2026 0:00	4	6	5581.65
Sponsor1	7/1/2026 0:00	4	7	5533.6
Sponsor1	8/1/2026 0:00	4	8	5485.98
Sponsor1	9/1/2026 0:00	4	9	5438.77
Sponsor1	10/1/2026 0:00	4	10	5391.97
Sponsor1	11/1/2026 0:00	4	11	5345.59
Sponsor1	12/1/2026 0:00	4	12	5299.6
Sponsor1	1/1/2027 0:00	5	1	5254.03
Sponsor1	2/1/2027 0:00	5	2	5219.4
Sponsor1	3/1/2027 0:00	5	3	5184.97
Sponsor1	4/1/2027 0:00	5	4	5150.8
Sponsor1	5/1/2027 0:00	5	5	5116.88
Sponsor1	6/1/2027 0:00	5	6	5083.17
Sponsor1	7/1/2027 0:00	5	7	5049.7
Sponsor1	8/1/2027 0:00	5	8	5016.46
Sponsor1	9/1/2027 0:00	5	9	4983.44
Sponsor1	10/1/2027 0:00	5	10	4950.64
Sponsor1	11/1/2027 0:00	5	11	4918.09
Sponsor1	12/1/2027 0:00	5	12	4885.75
Sponsor1	1/1/2028 0:00	6	1	4853.62
Sponsor1	2/1/2028 0:00	6	2	4821.73
Sponsor1	3/1/2028 0:00	6	3	4790.04
Sponsor1	4/1/2028 0:00	6	4	4758.59
Sponsor1	5/1/2028 0:00	6	5	4727.35
Sponsor1	6/1/2028 0:00	6	6	4696.34
Sponsor1	7/1/2028 0:00	6	7	4665.53
Sponsor1	8/1/2028 0:00	6	8	4634.91
Sponsor1	9/1/2028 0:00	6	9	4604.51
Sponsor1	10/1/2028 0:00	6	10	4574.33
Sponsor1	11/1/2028 0:00	6	11	4544.35
Sponsor1	12/1/2028 0:00	6	12	4514.58
Sponsor1	1/1/2029 0:00	7	1	4485
Sponsor1	2/1/2029 0:00	7	2	4456.19
Sponsor1	3/1/2029 0:00	7	3	4427.57
Sponsor1	4/1/2029 0:00	7	4	4399.16
Sponsor1	5/1/2029 0:00	7	5	4370.92
Sponsor1	6/1/2029 0:00	7	6	4342.89
Sponsor1	7/1/2029 0:00	7	7	4315.02
Sponsor1	8/1/2029 0:00	7	8	4287.34
Sponsor1	9/1/2029 0:00	7	9	4259.85
Sponsor1	10/1/2029 0:00	7	10	4232.53
Sponsor1	11/1/2029 0:00	7	11	4205.39
Sponsor1	12/1/2029 0:00	7	12	4178.45
Sponsor1	1/1/2030 0:00	8	1	4151.68
Sponsor1	2/1/2030 0:00	8	2	4125.11
Sponsor1	3/1/2030 0:00	8	3	4098.69
Sponsor1	4/1/2030 0:00	8	4	4072.44
Sponsor1	5/1/2030 0:00	8	5	4046.41
Sponsor1	6/1/2030 0:00	8	6	4020.5
Sponsor1	7/1/2030 0:00	8	7	3994.78
Sponsor1	8/1/2030 0:00	8	8	3969.25
Sponsor1	9/1/2030 0:00	8	9	3943.87
Sponsor1	10/1/2030 0:00	8	10	3918.65
Sponsor1	11/1/2030 0:00	8	11	3893.6
Sponsor1	12/1/2030 0:00	8	12	3868.74
Sponsor1	1/1/2031 0:00	9	1	3844.02
Sponsor1	2/1/2031 0:00	9	2	3819.47
Sponsor1	3/1/2031 0:00	9	3	3795.1
Sponsor1	4/1/2031 0:00	9	4	3770.86
Sponsor1	5/1/2031 0:00	9	5	3746.79
Sponsor1	6/1/2031 0:00	9	6	3722.89
Sponsor1	7/1/2031 0:00	9	7	3699.16
Sponsor1	8/1/2031 0:00	9	8	3675.55
Sponsor1	9/1/2031 0:00	9	9	3652.13
Sponsor1	10/1/2031 0:00	9	10	3628.84
Sponsor1	11/1/2031 0:00	9	11	3605.72
Sponsor1	12/1/2031 0:00	9	12	3582.75
Sponsor1	1/1/2032 0:00	10	1	3559.94
Sponsor1	2/1/2032 0:00	10	2	3537.27
Sponsor1	3/1/2032 0:00	10	3	3514.73
Sponsor1	4/1/2032 0:00	10	4	3492.36
Sponsor1	5/1/2032 0:00	10	5	3470.12
Sponsor1	6/1/2032 0:00	10	6	3448.06
Sponsor1	7/1/2032 0:00	10	7	3426.12
Sponsor1	8/1/2032 0:00	10	8	3404.32
Sponsor1	9/1/2032 0:00	10	9	3382.67
Sponsor1	10/1/2032 0:00	10	10	3361.18
Sponsor1	11/1/2032 0:00	10	11	3339.83
Sponsor1	12/1/2032 0:00	10	12	3318.6

Re: How to calculate IRR using DAX

OwenAuger — Thu, 10 Aug 2023 06:52:49 GMT

Hi sanjaymithran

I loaded your Data table exactly as in your post, and tried your IRR measure, and did get a result of 0.45%.

See attached PBIX.

To confirm, do you want to replicate the behaviour of the Excel IRR function, which takes an ordered list of cashflows and assumes they occur at 1-year intervals (in this case by ignoring the DATE column)?

You can also write this more simply as something like this:

IRR 2 = XIRR ( 'Data', Data[Cash Flow], RANK ( DENSE, ALL ( 'Data'[DATE] ) ) * 365 )

The interval between the Date values is important, rather than their magnitude, so there is no need to add the minimum date.

Please post back if needed.

Regards

Re: How to calculate IRR using DAX

sanjaymithran — Thu, 10 Aug 2023 14:11:10 GMT

Hi OwenAuger,

Thanks for reply,the below one is detail data for which i posted yesteday is summary,same formula apply here not getting correct result its show 0% instead of 0.45% we have country,sponsor and Launch date slicer

Not able to attach file will split send agail

topic How to calculate IRR using DAX in DAX Commands and Tips

How to calculate IRR using DAX

Re: How to calculate IRR using DAX

Re: How to calculate IRR using DAX