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.

v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
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.