Forum Discussion

alexcanton's avatar
alexcanton
Regular Visitor
4 years ago
Solved

Exponential Growth Formula on Time Series Data

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?

 

keywordSlopeSep 17Oct 17Nov 17Dec 17Jan 18Feb 18Mar 18Apr 18May 18Jun 18Jul 18Aug 18Sep 18Oct 18Nov 18Dec 18Jan 19Feb 19Mar 19Apr 19May 19Jun 19Jul 19Aug 19Sep 19Oct 19Nov 19Dec 19Jan 20Feb 20Mar 20Apr 20May 20Jun 20Jul 20Aug 20Sep 20Oct 20Nov 20Dec 20Jan 21Feb 21Mar 21Apr 21May 21Jun 21Jul 21Aug 21
walmart2705137M30M37M56M56M30M30M37M30M37M37M37M37M37M37M56M56M37M37M37M37M37M37M46M37M37M37M56M56M37M30M56M83M83M68M68M68M56M56M101M83M56M46M56M56M101M56M68M

 

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

Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    4 years ago

    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.

1 Reply

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    4 years ago

    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.