Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
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 |
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |