cancel
Showing results for
Did you mean:
Helper I

## Find current trend in time series

Hello,

I have a (sparse) time series table and I want to calculate the current trend based only on the last 3 values.

I think the slope of trend line (Linear Regression) could be considered as the current trend.

I' ve found code describing the DAX Linear Regression calculation but it is based on all previous values.

I know it might be silly but I'm stack.

Can you please suggest a workaround?

Thank you in advance.

 ID Datetime Value 1 1/1/2021 8:00:25 0.24 2 1/1/2021 9:45:10 0.24 3 1/1/2021 14:35:38 0.29 4 2/1/2021 11:12:00 0.28 5 2/1/2021 19:43:29 0.27 6 3/1/2021 7:17:12 0.26

1 ACCEPTED SOLUTION
Super User II

Hey @KyriakosT ,

i created this simple measure:

``````just the average value =
CALCULATE(
AVERAGE( 'Table'[Value] )
)``````

and this more complex one, here I use TOPN to find the previous 3 values

``````the average of the last 3 datetimes =
var __datetime = MAX( 'Table'[Datetime] )
return
CALCULATE(
[just the average value]
, TOPN(
3
, FILTER( ALL('Table'[Datetime] ) , 'Table'[Datetime] < __datetime )
, 'Table'[Datetime]
, DESC
)
, ALL('Table'[ID] , 'Table'[Value])
)``````

it seems that the 2nd measure creates what you are looking for

Hopefully, this provides what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Super User II

Hey @KyriakosT ,

i created this simple measure:

``````just the average value =
CALCULATE(
AVERAGE( 'Table'[Value] )
)``````

and this more complex one, here I use TOPN to find the previous 3 values

``````the average of the last 3 datetimes =
var __datetime = MAX( 'Table'[Datetime] )
return
CALCULATE(
[just the average value]
, TOPN(
3
, FILTER( ALL('Table'[Datetime] ) , 'Table'[Datetime] < __datetime )
, 'Table'[Datetime]
, DESC
)
, ALL('Table'[ID] , 'Table'[Value])
)``````

it seems that the 2nd measure creates what you are looking for

Hopefully, this provides what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors