Forum Discussion

y5famfnatudu's avatar
y5famfnatudu
Icon for Resolver I rankResolver I
2 years ago
Solved

Rolling difference (measure)

Greetings, I have a customer table with a measure computing the total revenue generated by each customer. Additionally, I've included a ranking column, sorting customers from the lowest to the highe...
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    y5famfnatudu , Use offset function

     

    Offset Previous Rank = CALCULATE(Sum([Revenue]),OFFSET(-1, ALL('Table'[Name],'Table'[Rank]),ORDERBY('Date'[Rank])))

     

     

    You can then take a diff

    sum(Table[Revenue]) - [Offset Previous Rank]

     

    I assumed Revenue is a column, if it is measure no need for Sum

     

    Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

y5famfnatudu , Use offset function

 

Offset Previous Rank = CALCULATE(Sum([Revenue]),OFFSET(-1, ALL('Table'[Name],'Table'[Rank]),ORDERBY('Date'[Rank])))

 

 

You can then take a diff

sum(Table[Revenue]) - [Offset Previous Rank]

 

I assumed Revenue is a column, if it is measure no need for Sum

 

Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U

  • y5famfnatudu's avatar
    y5famfnatudu
    Icon for Resolver I rankResolver I
    2 years ago

    Thank you amitchandak, but Rank is not a column in a table, rather a measure that I created.

    __CustomerRank =
RANKX ( ALLSELECTED ( 'Customers' ), [Revenue],, ASC, DENSE )