Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
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
@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
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |