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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TorsteinZahl
Frequent Visitor

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:

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
v-yalanwu-msft
Community Support
Community Support

Hi, @TorsteinZahl ;

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

vyalanwumsft_0-1645694240881.png

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:

vyalanwumsft_1-1645694299443.png

 


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.

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
Community Support

Hi, @TorsteinZahl ;

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

vyalanwumsft_0-1645694240881.png

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:

vyalanwumsft_1-1645694299443.png

 


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
Frequent Visitor

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
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors