Dynamic Market Return Calculations

[Warrior42]

Hello, I have a monthly return data table that populates each month with new monthly returns starting 1/1/1926.  Below is an example of how the table is laid out with the first column being the month and the following columns being each index monthly retun.  My hope was to create a way to quickly calculate either the monthly, quarterly, annual, 3 year annualized, 5 year annualized, 10 year annualized returns based on a custom date selected.  I'm pretty new at all of this and wouldnt even know where to start, is this possible? 

[v-pnaroju-msft]

Hi Warrior42,

We wanted to follow up as we haven’t yet received a response regarding your query. If you’ve managed to solve the issue, please consider sharing the solution with the community—it could really help others experiencing the same problem.

If any of the responses have been useful, kindly mark them as the accepted solution to guide other members.

If the problem is still unresolved, please provide sample data that clearly demonstrates the issue (avoid screenshots and exclude any sensitive or unrelated information). Also, let us know the expected result based on that sample.

Thank you.

[v-pnaroju-msft]

Hi Warrior42,

We have not yet received a response from you regarding your query and would like to follow up to know if it has been resolved. If you have found a solution, please share it with the community to help others facing similar issues.

If you find any response helpful, kindly mark it as the accepted solution to assist other members.

If the issue is unresolved, please provide sample data illustrating the problem in a usable format (not as a screenshot), ensuring no sensitive or unrelated information is included. Also, specify the expected outcome based on the sample data.

Thank you.

[v-pnaroju-msft]

Hi Warrior42,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.If you find the response helpful, please mark it as the accepted solution, as this will help other members with similar queries.
If the issue remains unresolved, we request you to kindly share sample data that clearly illustrates your problem or question in a usable format (not as a screenshot). Please ensure that no sensitive or unrelated information is included. Additionally, please specify the expected outcome based on the sample data provided.

Thank you.

[v-pnaroju-msft]

Thankyou, @AlexisOlson, @Ashish_Excel, for your response.

Hi Warrior42,

We appreciate your inquiry on the Microsoft Fabric Community Forum.

Based on my understanding of your query, please find attached a screenshot and a sample PBIX file that may assist in resolving the issue:

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will help other community members who may have similar queries. If the issue remains unresolved, we request you to kindly share sample data that clearly illustrates your problem or question in a usable format (not as a screenshot). Please ensure that no sensitive or unrelated information is included. Additionally, please specify the expected outcome based on the sample data provided.

Should you have any further questions, please feel free to reach out to the Microsoft Fabric Community.

Thank you.

[Ashish_Excel]

Hi,

Share the download link of the Excel file with your Excel formulas written there.  I will try to translate those in the DAX language.

[Warrior42]

I really apprecaite it!  So I'm not sure I can share of link of this being its on private network, but I did attache screenshots of the first few rows (note it currently goes back to 1/1/1925.  I know its not the most ideal so if there is a better way let me know.  Ultimately the data set I am working with which would be updated on a monthly basis with the new return values.  Starting in column L I have the forumulas used for calculating the 3 Mo, 1 year, 3 year, 5 year and 10 year returns based on each month.  I'm not too sure how cumulative return would be calculated for this view.  The ultimate goal is to have this data on a dash board and you can get these figures based on date that you choose.  Does this help or is there anything additional that could be more helpful?

[Ashish_Excel]

Hi,

I cannot work with screnshots.  I will need an MS Excel file with your formulas to understand your logic.

[AlexisOlson]

Definitely possible. I do this frequently. Here's the basic pattern to get you moving in the right direction:

EAFE 5Yr Annualized Return =
VAR SelectedDate = SELECTEDVALUE ( DateSelectionParameter[EndDate] )
VAR StartDate = EOMONTH ( SelectedDate, -5 * 12 )
VAR DateRange =
    FILTER (
        SUMMARIZE ( MthlyIndxRet, MthlyIndxRet[Date], MthlyIndxRet[EAFE] ),
        MthlyIndxRet[Date] <= SelectedDate && MthlyIndxRet[Date] > StartDate
    )
VAR LinkedReturn = PRODUCTX ( DateRange, 1 + MthlyIndxRet[EAFE] )
VAR AnnualizedReturn = LinkedReturn ^ ( 1 / 5 ) - 1
RETURN
    AnnualizedReturn