Forum Discussion
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 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
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
2 Replies
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
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 )
