cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Scaling time series data based on starting date value

Hi all,

 

I'm struggling with a probably relatively easy problem:

I have time series data (e.g. stock prices) with 3 variables: date, stock name (Aktie) and value.

stock price 1.PNG

In this example I have just 2 categories, which have very different values (first one around 1500 and the 2nd one around 10). So if I plot them on a simple line chart, it looks quite ugly as you don't see any movement.

 

stock price 2.PNG

So the idea is to scale both values so that they start at the same value (say 1,0) and then indicate the trend compared to that value. This scaled time series can't be saved as a calculated column, because the plotted date period should be dynamic (set by a slicer). 

The result should look like this.

stock price 3.PNG

I was able to achieve this plot by using the following measures:

FirstValue = CALCULATE(SUM(Tabelle1[Wert]);FILTER(ALL(Datumstabelle);Datumstabelle[Date]=DATE(2020;1;1)))

ScaledValue = DIVIDE(SUM(Tabelle1[Wert]);[FirstValue];0)a

 

Unfortunately I wasn't able to set the DATE(2020;1;1) to the minimum value in the current filter context. Whatever I tried, PowerBI set the filter context to the date on the x-axis in the line chart, resulting in the scaledValue being 1 every day for both stocks.

 

Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Scaling time series data based on starting date value

6 REPLIES 6
Highlighted
Anonymous
Not applicable

Re: Scaling time series data based on starting date value

Highlighted
Frequent Visitor

Re: Scaling time series data based on starting date value

Perfect, thanks a lot!

I have one more question about the same use case:

We also want to display the difference (absolute or in percent) between the value on the current date and the value on the previous available date.
I tried to work with the EARLIER function, but it seems to require an ordered column and I don't know how to create one if there are several stocks in the dataset.

Highlighted
Anonymous
Not applicable

Re: Scaling time series data based on starting date value

I think they've blocked my account here (or will be soon), so I might not be able to craft the solution quickly enough.

Sorry but it's the admin of this site that's to blame.

Best
D
Highlighted
Anonymous
Not applicable

Re: Scaling time series data based on starting date value

OK, use the same link. I've updated the file.

Best
D
Highlighted
Anonymous
Not applicable

Re: Scaling time series data based on starting date value

By the way... EARLIER has nothing to do with any order. It's a function (which should not be used) that lets you access an outer row context. There are however better ways to do such an operation: VARiables.

Best
D
Highlighted
Anonymous
Not applicable

Re: Scaling time series data based on starting date value

Well, has the updated file worked for you?

Best
D

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.