Forum Discussion

rjiang's avatar
rjiang
Icon for Helper I rankHelper I
2 years ago

Index to 100

I have a table with the following fields: Date, Sector, CPPI
 
Some sectors have data going back further than others. I want to be able to show this in a multiple line chart by sector, and indexing all sectors to 100 on the latest available start date for the sectors selected.

For example, lets say sector 1, sector 2, and sector 3 have data that begins in 1/1/2020, 1/1/2021, and 1/1/2022 respectively.
If sector 1 and sector 2 are selected, I want CPPI for both sector 1 and 2 to be indexed to 100 on 1/1/2021
If sector 2 and sector 3 are selected, I want CPPI for both sector 2 and 3 to be indexed to 100 on 1/1/2022
If sector 1, 2 and 3 are selected, I want CPPI for all sectors to be indexed to 100 on 1/1/2022

Below is the measure I've come up with, but it indexes each sector to 100 at the beginning of each series' first available date.

Index =
VAR SelectedSectors = VALUES('Sector CPPI'[Sector])
VAR LatestSelectedSector = MAXX(SelectedSectors, 'Sector CPPI'[Sector])
VAR EarliestDate =
    CALCULATE(
        MIN('Sector CPPI'[Date_bom1]),
        ALLSELECTED('Sector CPPI'),
        'Sector CPPI'[Sector] = LatestSelectedSector
    )
VAR CPPI_at_EarliestDate =
    CALCULATE(
        MIN('Sector CPPI'[CPPI]),
        'Sector CPPI'[Date_bom1] = EarliestDate,
        'Sector CPPI'[Sector] = LatestSelectedSector
    )
RETURN
    SUM('Sector CPPI'[CPPI]) / CPPI_at_EarliestDate * 100
Need Help

1 Reply