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)] )
    )
)

     

     

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

Hi BMltc ,

 

Don't know if you want to show the two  values but you can do the following:

  • Add a calendar table to your model
  • Create the following measures:
Average value =
IF (
    SELECTEDVALUE ( 'Table'[Date] ) = MAX ( 'Calendar'[Date] ),
    CALCULATE ( AVERAGE ( 'Table'[Sales] ) )
)


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


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


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


Ranking Difference = [Ranking] - [Previous Ranking]

 

Final result below:

PBIX file attach.

 

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

    thank you MFelix , that is almost perfect! I have noticed that you have filtered the table

    is it possible to have the same table without this filter?

    the concept is that date will change and i want the latest date and compare the data a week ago.

     

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

      Hi BMltc ,

       

      If you don't want  to filter the table, then you don't need the calendar table, redo the measures to:

       

      Average value = 
    CALCULATE ( AVERAGE ( 'Table'[Sales] ) )



Average value (previous) = 
    CALCULATE (
        [Average value],
        FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] = MAX ( 'Table'[Date] ) - 1 )
    )


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



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


Ranking Difference = [Ranking] - [Previous Ranking]

       

       

      • 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