How to calculate IRR using DAX

[sanjaymithran]

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

[OwenAuger · Accepted solution]

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

[sanjaymithran]

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