Forum Discussion

BMltc's avatar
BMltc
Icon for Helper II rankHelper II
4 years ago
Solved

RANKX on a specific date

Hi Community,   I know that this question has been asked many time but I can't find my answer. I have a list of sales by product, by location and by date.  Date Product Location Sales 20/...
Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    Hi BMltc ,

     

    The you just need to add a calculation to precede your measures to check if it's the maximum date:

     

    Average value = 
var MaximumDate = CALCULATE(MAX('Table'[Date]),ALL('Table'[Date]))
Return
IF(SELECTEDVALUE('Table'[Date]) = MaximumDate,
    CALCULATE ( AVERAGE ( 'Table'[Sales] ) )
)

Average value (previous) = 
var MaximumDate = CALCULATE(MAX('Table'[Date]),ALL('Table'[Date]))
Return
IF(SELECTEDVALUE('Table'[Date]) = MaximumDate,
    CALCULATE (
        AVERAGE('Table'[Sales]),
        FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] = MAX ( 'Table'[Date] ) - 1 )
    )
)

Ranking = 
IF([Average value] <> BLANK(),
    RANKX (
        FILTER (
            SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Date], 'Table'[Product] ),
            'Table'[Date] = MAX ( 'Table'[Date] )
        ),
        CALCULATE ( 'Table'[Average value] )
    )
)

Previous Ranking = 
IF(
    'Table'[Average value] <> Blank(),
    RANKX (
        FILTER (
            SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Date], 'Table'[Product] ),
            'Table'[Date] = MAX ( 'Table'[Date] )
        ),
        CALCULATE ( 'Table'[Average value (previous)] )
    )
)

     

     

BMltc's avatar
BMltc
Icon for Helper II rankHelper II
4 years ago

Thanks again MFelix, sorry but my last request was not clear. In this example, my dataset is from 20/12 to 22/12 but the day after, it will be updated from 23/12 to 20/12 (and the day after 24/12 to 20/12). And I want the table you have done 

with the lastest date compared to 1 day before. is it clear?

 

Thanks again

 

MFelix's avatar
MFelix
Icon for Super User rankSuper User
4 years ago

Hi BMltc ,

 

The you just need to add a calculation to precede your measures to check if it's the maximum date:

 

Average value = 
var MaximumDate = CALCULATE(MAX('Table'[Date]),ALL('Table'[Date]))
Return
IF(SELECTEDVALUE('Table'[Date]) = MaximumDate,
    CALCULATE ( AVERAGE ( 'Table'[Sales] ) )
)

Average value (previous) = 
var MaximumDate = CALCULATE(MAX('Table'[Date]),ALL('Table'[Date]))
Return
IF(SELECTEDVALUE('Table'[Date]) = MaximumDate,
    CALCULATE (
        AVERAGE('Table'[Sales]),
        FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] = MAX ( 'Table'[Date] ) - 1 )
    )
)

Ranking = 
IF([Average value] <> BLANK(),
    RANKX (
        FILTER (
            SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Date], 'Table'[Product] ),
            'Table'[Date] = MAX ( 'Table'[Date] )
        ),
        CALCULATE ( 'Table'[Average value] )
    )
)

Previous Ranking = 
IF(
    'Table'[Average value] <> Blank(),
    RANKX (
        FILTER (
            SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Date], 'Table'[Product] ),
            'Table'[Date] = MAX ( 'Table'[Date] )
        ),
        CALCULATE ( 'Table'[Average value (previous)] )
    )
)

 

 

  • BMltc's avatar
    BMltc
    Icon for Helper II rankHelper II
    4 years ago

    Really helpful, many thanks