Solved: Exponential Growth Formula on Time Series Data

alexcanton · ‎09-17-2021

I'm looking for some support, Sorting rows of time series data by highest exponential growth. I am currently using =SLOPE in Excel but some trends have peaked and they still rank highly, so I'm trying to find a more nuanced approach.

As an example of a data subset, the most recent fall of walmart in Jul21 falls almost half. I would rather inlcude rows at the top of my data that are consectively growing not growing over time then falling recently.

Any suggestions for formula? I'm guessing it needs weighting added to recent months?

keyword

Slope

Sep 17

Oct 17

Nov 17

Dec 17

Jan 18

Feb 18

Mar 18

Apr 18

May 18

Jun 18

Jul 18

Aug 18

Sep 18

Oct 18

Nov 18

Dec 18

Jan 19

Feb 19

Mar 19

Apr 19

May 19

Jun 19

Jul 19

Aug 19

Sep 19

Oct 19

Nov 19

Dec 19

Jan 20

Feb 20

Mar 20

Apr 20

May 20

Jun 20

Jul 20

Aug 20

Sep 20

Oct 20

Nov 20

Dec 20

Jan 21

Feb 21

Mar 21

Apr 21

May 21

Jun 21

Jul 21

Aug 21

walmart

27051

37M

30M

37M

56M

30M

37M

30M

37M

56M

37M

46M

37M

56M

37M

30M

56M

83M

68M

56M

101M

83M

56M

46M

56M

101M

56M

68M

https://docs.google.com/spreadsheets/d/12I9JQe313NepHjkn28VRWOjJNs9KJELiFwUZNjwLlhs/edit?usp=sharing

v-henryk-mstf · ‎09-20-2021

Hi @alexcanton ,

I tried the following test to extract the dates in the data model, form a date table separately, and then create a measure to find the corresponding maximum value for each date. Put it in the corresponding column chart visual.

M =
VAR a =
    MAXX ( TOPN ( 1, Dim_Table, Dim_Table[Date] ), Dim_Table[Value] )
RETURN
    CALCULATE (
        MAX ( Dim_Table[Value] ),
        FILTER (
            ALL ( Dim_Table ),
            Dim_Table[Date] = MAX ( Dim_Table[Date] )
                && a = Dim_Table[Value]
        )
    )

But it seems that there are still doubts whether you can clarify your needs or provide the desired results. Or i can answer for you as soon as possible. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-henryk-mstf · ‎09-20-2021

Hi @alexcanton ,

I tried the following test to extract the dates in the data model, form a date table separately, and then create a measure to find the corresponding maximum value for each date. Put it in the corresponding column chart visual.

M =
VAR a =
    MAXX ( TOPN ( 1, Dim_Table, Dim_Table[Date] ), Dim_Table[Value] )
RETURN
    CALCULATE (
        MAX ( Dim_Table[Value] ),
        FILTER (
            ALL ( Dim_Table ),
            Dim_Table[Date] = MAX ( Dim_Table[Date] )
                && a = Dim_Table[Value]
        )
    )

But it seems that there are still doubts whether you can clarify your needs or provide the desired results. Or i can answer for you as soon as possible. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.