Rebase time series based on selected date

[RCHANG44]

Hi,

I am working to rebase multiple time series data to 100 on the selected base date. However, the result is not what I expected. It always rebased to the very first date instead of respecting my selection.



Here is my measure:

REBASED_VAL =

VAR measure_dt = calculate(min(unpivot_historical[Date_yyyymmdd]),ALLEXCEPT(unpivot_historical,unpivot_historical[Attribute]))
VAR rebase =
    CALCULATE(
    MAX(unpivot_historical[Value]),
    FILTER(ALLEXCEPT(unpivot_historical,unpivot_historical[Attribute]),unpivot_historical[Date_yyyymmdd]=measure_dt)
)
VAR a =
    CALCULATE(
    MAX(unpivot_historical[Value])
)
RETURN
DIVIDE(a,rebase)*100

Thanks!

[Anonymous · Accepted solution]

@RCHANG44 

Before you start using ALLEXCEPT... I'd strongly recommend you read THIS. ALLEXCEPT is a very deceiving function and you have to understand it well to be able to use it correctly. I'm not getting deep into your issue but I'd also suggest you read upon ALLSELECTED. It might be necessary to use it in this context.

[RCHANG44]

Thank you @Anonymous .

Just wanted to share it works as follows:

rb_tbl =

VAR a =
    CALCULATE (
        MAX ( unpivot_historical[Value] ),
        FILTER (
            unpivot_historical,
            unpivot_historical[Date_yyyymmdd] = MIN(unpivot_historical[Date_yyyymmdd]))
        )
   

VAR firstrecord =
    CALCULATE (
        MAX ( unpivot_historical[Value]),
        FILTER (
            ALLSELECTED ( unpivot_historical ),
            unpivot_historical[Date_yyyymmdd]= MIN('NEWCALENDAR Tbl'[Date]) &&unpivot_historical[Attribute]=SELECTEDVALUE(unpivot_historical[Attribute]))
    )
RETURN
    DIVIDE(a,firstrecord)*100

[bpb11]

Please can you share the NEWCALENDAR Tbl creation (and any addiitonal measure). I am trying to replicate this and struggling to get the dynamic start date to populate the table. 

Many thanks in advance.