How To Determine Top Ranking Sales Category for a Specific Time Period

[Anonymous]

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? 

[Anonymous]

Thanks VahidDM

[VahidDM · Accepted solution]

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/