Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

  • 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/

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable

    Thanks VahidDM