topic Re: XIRR and Date Table in DAX Commands and Tips

XIRR and Date Table

TorsteinZahl — Mon, 21 Feb 2022 12:42:37 GMT

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

Re: XIRR and Date Table

amitchandak — Mon, 21 Feb 2022 13:00:04 GMT

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

Re: XIRR and Date Table

TorsteinZahl — Mon, 21 Feb 2022 13:51:38 GMT

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

Re: XIRR and Date Table

v-yalanwu-msft — Thu, 24 Feb 2022 09:18:27 GMT

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.