Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
TorsteinZahl
Frequent Visitor

XIRR with date table will not work properly

I have an issue with Star Modelling my XIRR Formula.. I can't simply get it to work.. I have tried and tried, so decided to ask here. 

 

My Fact data is:

TorsteinZahl_0-1645432376134.png

 

My Relationsship with dates are like this:

TorsteinZahl_1-1645432424627.png

 

Im trying to Calculate the formula XIRR per Quarter and with a Quarter slicer for futher calculations. 

My fact table dont have "first value" in minus like the XIRR formula needs. So I took a work around with this DAX in order to get PreviousQuarter Lastdate Value in to the table.

TorsteinZahl_3-1645432841355.png

 

And this is the Correct table for calculating XIRR!! I have an minus value to start and cashflow in pluss and value at end of period. 

However my XIRR woould not wtok... I tried this as my "best" failure. 

TorsteinZahl_4-1645432937054.png
TorsteinZahl_5-1645432946300.png

If anyone can help me with this XIRR problem I would be so thankfull!! 

Dont hesitate to ask 🙂 Links to files:
https://drive.google.com/drive/folders/1quE7m429GwhcSJEGJi5HdmeWHqeavuu-?usp=sharing

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

HI @TorsteinZahl,

I think this should be related to your filter, one or more records have been filtered on your visual.
Then XIRR function will process the filtered records which include blank parts at 'value' and 'date' arguments. (these values should not be suitable for the function calculation)

Measure concatenate current 'Previous Quarter Value in Minus' and date field values:

1.png

Fixed measure formal with the condition and alternate results:

XIRR = 
VAR currDate =
    MAX ( fact_values[Date] )
VAR currPQ = [Previous Quarter Value in Minus]
RETURN
    IF (
        AND ( currPQ <> BLANK (), currDate <> BLANK () ),
        XIRR ( fact_values, [Previous Quarter Value in Minus], [Date] ,,BLANK())
    )

Regards,

Xiaoxin Sheng

View solution in original post

1 REPLY 1
Anonymous
Not applicable

HI @TorsteinZahl,

I think this should be related to your filter, one or more records have been filtered on your visual.
Then XIRR function will process the filtered records which include blank parts at 'value' and 'date' arguments. (these values should not be suitable for the function calculation)

Measure concatenate current 'Previous Quarter Value in Minus' and date field values:

1.png

Fixed measure formal with the condition and alternate results:

XIRR = 
VAR currDate =
    MAX ( fact_values[Date] )
VAR currPQ = [Previous Quarter Value in Minus]
RETURN
    IF (
        AND ( currPQ <> BLANK (), currDate <> BLANK () ),
        XIRR ( fact_values, [Previous Quarter Value in Minus], [Date] ,,BLANK())
    )

Regards,

Xiaoxin Sheng

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.