Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am using Power BI/DAX to calculate linked returns for a portfolio.
We have 2 different return fields -- one for daily return and one for monthly returns.
Theoretically, they should return the same value.
The DAX formulas are almost exactly the same except the MTD has an additional FILTER -- where IsMTD=TRUE
THIS WORKS
msr_LinkedReturn_Daily = VAR DATECOL= MAX(AccountReturn[AccountReturn_AsOfDate]) RETURN (CALCULATE( PRODUCT( AccountReturn[calc_Return_Daily] ), FILTER( AccountReturn, AccountReturn[AccountReturn_AsOfDate]<=DATECOL ) )-1)
THIS DOES NOT WORK
msr_LinkedReturn_MTD = VAR DATECOL= MAX(AccountReturn[AccountReturn_AsOfDate]) RETURN (CALCULATE( PRODUCT( AccountReturn[calc_Return_MTD] ),FILTER(AccountReturn,AccountReturn[IsLastDayOfMonth]=TRUE), FILTER( AccountReturn, AccountReturn[AccountReturn_AsOfDate]<=DATECOL ) )-1)
The only real difference between these 2 formulas (besides a different field value being used) is the MTD has 2 filter fields -- the daily only has one. The additional filter in MTD is: FILTER(AccountReturn,AccountReturn[IsLastDayOfMonth]=TRUE),
The MTD does sometimes work when I only rollup for a few portfolios -- but when I select more than 3 or 4 everything gets set to -100%.
Any help you can offer is greatly appreciated.
Thanks,
Dan
Solved! Go to Solution.
Hi Lydia,
Thanks for your response.
The field was a boolean (true/false). I found the error was in setting the variable at the start of the function. That was not required as that filter was already applied by defualt.
So, this issue has been resolved. Was user error.
Thanks,
Dan
@dancarr22,
What is the data type of IsLastDayOfMonth field? Is it text or True/False? You need to change the additional FILTER formula to the following if it is text:
FILTER(AccountReturn,AccountReturn[IsLastDayOfMonth]="TRUE")
If the above step doesn't help, please share sample data of the AccountReturn table here.
Regards,
Lydia
Hi Lydia,
Thanks for your response.
The field was a boolean (true/false). I found the error was in setting the variable at the start of the function. That was not required as that filter was already applied by defualt.
So, this issue has been resolved. Was user error.
Thanks,
Dan
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |