XIRR and Date Table

[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

[v-yalanwu-msft · Accepted solution]

Hi, @TorsteinZahl ;

You could delete the relationship or  create a new table without relationship.

then create a measure.

Previous Quarter Value in Minus 2 = 
IF (
    QUARTER ( MAX ( [date] ) ) = QUARTER ( MAX ( 'dim_date'[Date] ) )
        && YEAR ( MAX ( [date] ) ) = YEAR ( MAX ( 'dim_date'[Date] ) ),
    SUM ( [sum] ),
    CALCULATE ( - SUM ( [sum] ), LASTDATE ( PREVIOUSQUARTER ( dim_date[Date] ) ) ))

XIRR = XIRR(ALL('Table'),[Previous Quarter Value in Minus 2],[date])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TorsteinZahl]

Thanks for the reply. I have seen your video and thanks for your video in Excel and for the Curbal video. Both those examples just calculate directly on one table. I have not seen how to incorporate the Star Schema / Date Table

[amitchandak]

@TorsteinZahl , I have not tried this, I tried a few other financial functions.

Please refer to these two videos that can help

https://www.youtube.com/watch?v=N1WRSFuaFmE

https://www.youtube.com/watch?v=rHZ4hj5eh10