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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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. 

 

IDDatetime Value
11/1/2021 8:00:25 0.24
21/1/2021 9:45:10 0.24
31/1/2021 14:35:38 0.29
42/1/2021 11:12:00 0.28
52/1/2021 19:43:29 0.27
63/1/2021 7:17:12 0.26

 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey @Anonymous ,

 

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

image.png

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

View solution in original post

1 REPLY 1
TomMartens
Super User
Super User

Hey @Anonymous ,

 

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

image.png

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.