Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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
Hopefully, this provides what you are looking for.
Regards,
Tom
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
Hopefully, this provides what you are looking for.
Regards,
Tom
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |