cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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:

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!!

1 ACCEPTED SOLUTION
Community 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.

3 REPLIES 3
Community 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.

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

Super User

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

Please refer to these two videos that can help

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors