XIRR with date table will not work properly

[TorsteinZahl]

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:

My Relationsship with dates are like this:

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.

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. 

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

[Anonymous · Accepted solution]

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:

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