Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
RCHANG44
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.

dax_q.PNG

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

@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.

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


dax_q_solution.PNG

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.