DAX 2 project

Objective To provide a DAX formula to calculate a market cap weighted aggregate total return index in PowerPivot. The inputs for the calculation are: - Monthly market caps - A total return series The market caps can be used to determine the market cap weight. (See columns E:F in the attached workbook). The return indices have to be rebased before they can be aggregated. (See columns M:N in the attached workbook, as well as columns I:L, which offer an additional sample calculation). I have used Excel’s SUMPRODUCT function to calculate the market cap weighted aggregate return index. (See column O in the attached workbook.) Deliverables 1. DAX measure to calculate the MCap weight 2. DAX measure to rebase the return indices 3. DAX measure to calculate a market cap weighted aggregate return index, which uses the DAX measures from 1 and 2 Sample file attached The attached sample file contains some data and calculation examples, along with a data table which has been added to the PowerPivot data model. Note that the DAX formulas will ultimately be implemented in a data model that also includes a separate dates table. Please therefore add a dates table to the PowerPivot data model and reference this in the DAX formulas.