Rolling difference (measure)

[y5famfnatudu]

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 highest revenue.

I am seeking to create a new measure that calculates the percentage difference from the lowest revenue to the highest.

Kindly note that I can't create calculated columns

Best regards,
Simon

[amitchandak · Accepted solution]

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

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 )