The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 | 56M | 30M | 30M | 37M | 30M | 37M | 37M | 37M | 37M | 37M | 37M | 56M | 56M | 37M | 37M | 37M | 37M | 37M | 37M | 46M | 37M | 37M | 37M | 56M | 56M | 37M | 30M | 56M | 83M | 83M | 68M | 68M | 68M | 56M | 56M | 101M | 83M | 56M | 46M | 56M | 56M | 101M | 56M | 68M |
https://docs.google.com/spreadsheets/d/12I9JQe313NepHjkn28VRWOjJNs9KJELiFwUZNjwLlhs/edit?usp=sharing
Solved! Go to Solution.
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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
78 | |
77 | |
46 | |
39 |
User | Count |
---|---|
137 | |
108 | |
64 | |
64 | |
53 |