Forum Discussion
XIRR and Date Table
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
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.
3 Replies
v-yalanwu-msftCommunity Support
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.- amitchandak
Super User
TorsteinZahl , I have not tried this, I tried a few other financial functions.
Please refer to these two videos that can help
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
