cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## Rebase time series based on selected date

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]))RETURNDIVIDE(a,rebase)*100

Thanks!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

3 REPLIES 3
Anonymous
Not applicable

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.

Frequent Visitor

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

New Member

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.