compound
1 Topic
Compound results up depending on date hierarchy
Hi all, I have a dashboard where the user selects a combination of assets (a list of several hundred options) and the returns over time are shown on a line chart. The user can then drill down across a date hierarchy to see annual, quarterly and monthly returns. The data for the returns calculation is on a monthly basis and the returns are calculated from the underlying data components (ie. numerator and denominator are measures which are calculated depending on the selection and time period) for accuracy rather than a weighted average of the returns. Ideally I want Power BI to calculate the monthly return and then show higher hierarchy levels as a compound of the monthly. The steps would therefore be as follows: User selects which assets they want using a slicer Power BI calculates the monthly returns Power BI then calculates the compounded returns to display at quarterly/annual level. My first thought was to put the calculated monthly returns into a table and then compound and display those results in the chart but I don't think the tables are that clever in Power BI (I could be mistaken). Is there a clever way to do the above in DAX? Dataset is sensitive but I can whip together a really simple example if it helps.Solved3.3KViews0likes10Comments