Forum Discussion
Portfolio Returns
- I have data covering three years of monthly returns starting in March 2015. 2016 is the only full year of data and I can't work how to produce a table which has a partial year (2015), a full year (2016) and a year (2017) that requires a YTD calculation. The monthly NAV performance is calculated using (Current month's NAV - Previous Month NAV -1). The table also requires a cumulative total at year end - the cumulative total for the first year is calculated using the Dec 2015 NAV/Feb NAV 2015(the base)-1
- The Data has to be arrange as per the diagram below with the monthly performance (Current Month NAV/ Previous Month NAV-1 for each monthly cell and a cumulative year to date figure for the year
- In a full year the Yearly NAV is calculated as the closing NAV in Dec/Previous Dec's NAV-1
Jan | Feb | March | April | May | June | July | Aug | Sept | Oct | Nov | Dec | Year |
|
|
|
|
|
|
|
|
|
|
|
|
|
- The first NAV is 31st March 2015 10,123.83, Feb is used as the base hence NAV = 10,000
- In order to calculate 2015 NAV I need to divide Dec NAV (9613.70/10,000-1) = -3.86%
- In 2016 I have a full year so the year end calculation would be Dec 2016 closing NAV 11,190.96/Dec 2015 Closing NAV 9613.70 - 1 = 16.41%
- 2017 is also a partial year (the NAV runs up to Sept 2017) so the YTD calculation would be 11,949.31/11,190.96-1=6.78%
- Attached is the monthly returns in excel https://1drv.ms/x/s!AiRs1BCuVWhM6EfOsWkw52AbDaq-
- Sample PBIX file https://1drv.ms/u/s!AiRs1BCuVWhM6ElX82MVsTRvdPr5
HI Anonymous,
You can try to use below formula to achieve your requirement:
Measrue:
Result = VAR maxdate = MAXX ( FILTER ( ALL ( EMCD ), YEAR ( [Date] ) = MAX ( DimDate[Year] ) ), [Date] ) VAR current_dec = SUMX ( FILTER ( ALL ( EMCD ), FORMAT ( [Date], "yyyy/mm" ) = FORMAT ( maxdate, "yyyy/mm" ) ), [Absalon EM Corporate Debt SICAV NAV] ) VAR temp = SUMX ( FILTER ( ALL ( EMCD ), FORMAT ( [Date], "yyyy/mm" ) = FORMAT ( DATE ( YEAR ( maxdate ) - 1, MONTH ( maxdate ), 1 ), "yyyy/mm" ) ), [Absalon EM Corporate Debt SICAV NAV] ) VAR previous_dec = IF ( temp <> BLANK (), temp, LOOKUPVALUE ( EMCD[Absalon EM Corporate Debt SICAV NAV], EMCD[Date], MINX ( FILTER ( ALL ( EMCD ), [Absalon EM Corporate Debt SICAV NAV] <> BLANK () ), [Date] ) ) ) RETURN IF ( MAX ( [Absalon EM Corporate Debt SICAV NAV] ) <> BLANK (), IF ( current_dec / previous_dec <> BLANK (), current_dec / previous_dec - 1 ) )Notice: if your data contains any privacy data, please do mask sensitive data before sharing.
Regards,
Xiaoxin Sheng
Ashish thanks for your help. You have supplied a spreadsheet with the calculation which I already have. What I was looking to do was work out the syntax in Dax to reproduce the excel formula. Running the calculation in excel is straightforward, running it in Dax requires a lot more knowledge of the language eg the use of Earier, etc. I'm not there yet, but thanks for your help.
9 Replies
HI Anonymous,
You can try to use below formula to achieve your requirement:
Measrue:
Result = VAR maxdate = MAXX ( FILTER ( ALL ( EMCD ), YEAR ( [Date] ) = MAX ( DimDate[Year] ) ), [Date] ) VAR current_dec = SUMX ( FILTER ( ALL ( EMCD ), FORMAT ( [Date], "yyyy/mm" ) = FORMAT ( maxdate, "yyyy/mm" ) ), [Absalon EM Corporate Debt SICAV NAV] ) VAR temp = SUMX ( FILTER ( ALL ( EMCD ), FORMAT ( [Date], "yyyy/mm" ) = FORMAT ( DATE ( YEAR ( maxdate ) - 1, MONTH ( maxdate ), 1 ), "yyyy/mm" ) ), [Absalon EM Corporate Debt SICAV NAV] ) VAR previous_dec = IF ( temp <> BLANK (), temp, LOOKUPVALUE ( EMCD[Absalon EM Corporate Debt SICAV NAV], EMCD[Date], MINX ( FILTER ( ALL ( EMCD ), [Absalon EM Corporate Debt SICAV NAV] <> BLANK () ), [Date] ) ) ) RETURN IF ( MAX ( [Absalon EM Corporate Debt SICAV NAV] ) <> BLANK (), IF ( current_dec / previous_dec <> BLANK (), current_dec / previous_dec - 1 ) )Notice: if your data contains any privacy data, please do mask sensitive data before sharing.
Regards,
Xiaoxin Sheng
What if the NAV column was based on a measure?
Is it possible to make the calculation?
Hi Anonymous,
You have started another thread with the same question. I have answered your question in that thread. Download my solution from here.
Ashish thanks for your help. You have supplied a spreadsheet with the calculation which I already have. What I was looking to do was work out the syntax in Dax to reproduce the excel formula. Running the calculation in excel is straightforward, running it in Dax requires a lot more knowledge of the language eg the use of Earier, etc. I'm not there yet, but thanks for your help.
Hi,
I have solved the problem using only and only DAX i.e. PowerPivot in MS Excel. Please check the file at that link. This entire model can be taken to PowerBI desktop with just one click.
I'm trying to adapt this code to a table which has a half dozen funds, so I tried adding a filter on the name of the funds, but I got this error:
Calculation error in measure 'AllMeasures'[Result]: A table of multiple values was supplied where a single value was expected.
So I modified code like this:
VAR maxdate = MAXX ( FILTER ( FILTER(PortfolioPerf, PortfolioPerf[DisplayName] = "Growth"), YEAR ( [PerformanceDate] ) = MAX ( DateDim[Year] ) ), [PerformanceDate] )Do I need to break the table up one per fund, or can I add a filter to this code like I'm attempting?
Hi,
Try this measure
VAR maxdate = MAXX ( FILTER ( CALCULATETABLE(PortfolioPerf, PortfolioPerf[DisplayName] = "Growth"), YEAR ( [PerformanceDate] ) = MAX ( DateDim[Year] ) ), [PerformanceDate] )Thanks I will, my boss actually changed his mind, but yeah we have this basic problem but multiple return streams in the same table. So I thought I could break it up or filter. My DAX was never the best and probably is rusty, thanks a lot.
