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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
alexcanton
Regular Visitor

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 

1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

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]
        )
    )

vhenrykmstf_0-1632193021900.png


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

1 REPLY 1
v-henryk-mstf
Community Support
Community Support

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]
        )
    )

vhenrykmstf_0-1632193021900.png


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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.