Forum Discussion
How To Determine Top Ranking Sales Category for a Specific Time Period
Hello everyone,
I am having some trouble with filtering the top ranking category (by sales) for a specific time period. From previous tips on this forum, the top-ranking category for all sales can be found by:
Category Top =
VAR _A =
FILTER (
ADDCOLUMNS (
'Sales',
"Rank",
RANKX (
ALLSELECTED ( 'Sales'[Category] ),
CALCULATE ( SUM ( 'Sales'[Sales Amount] ) ),
,
DESC,
DENSE
)
),
[Rank] = 1
)
RETURN
MAXX ( _A, [Category] )
I am having trouble filtering this down for a specific time period (say 7 days). To attempt to tackle this, I have tried replacing:
CALCULATE ( SUM ( 'Sales'[Sales Amount] ) )
with
CALCULATE ( SUM ( 'Sales'[Sales Amount] ), DATESINPERIOD('Sales'[DATE], TODAY(), -7, DAY)
But this doesn't seem to produce the results I am looking for.
Any tips?
HI Anonymous
Try this:
Category Top = VAR _A = FILTER ( ADDCOLUMNS ( 'Sales', "Rank", RANKX ( 'Sales', CALCULATE ( SUM ( 'Sales'[Sales Amount] ), DATESINPERIOD ( 'Sales'[DATE], TODAY (), -7, DAY ) ), , DESC, DENSE ) ), [Rank] = 1 ) RETURN MAXX ( _A, [Category] )If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
2 Replies
- VahidDM
Super User
HI Anonymous
Try this:
Category Top = VAR _A = FILTER ( ADDCOLUMNS ( 'Sales', "Rank", RANKX ( 'Sales', CALCULATE ( SUM ( 'Sales'[Sales Amount] ), DATESINPERIOD ( 'Sales'[DATE], TODAY (), -7, DAY ) ), , DESC, DENSE ) ), [Rank] = 1 ) RETURN MAXX ( _A, [Category] )If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/ - AnonymousNot applicable
Thanks VahidDM