Forum Discussion

TorsteinZahl's avatar
TorsteinZahl
Frequent Visitor
4 years ago
Solved

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 l...
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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's avatar
TorsteinZahl
Frequent Visitor
4 years ago

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