How to calculate dynamic index

[adinarayana123]

Hi,

I have sales of a products at different time points. Now I want to plot the sales in power bi desktop based on index instead of Time as mentioned in the below table:

Calculating index dynamically for diffeent products is a challenge for me. Need your advise on how to achieve this.

Product	Time	Index
Product1	201801	0
Product1	201802	1
Product1	201803	2
Product1	201804	3
Product2	201803	0
Product2	201804	1
Product2	201805	2
Product2	201806	3
Product2	201807	4

[v-rzhou-msft · Accepted solution]

Hi @adinarayana123 

Try this code to build a calculated column in your table.

dynamic Index each product = 
RANKX(FILTER('Table','Table'[Product]=EARLIER('Table'[Product])),'Table'[Time],,ASC,Dense)-1

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@adinarayana123 , where is a product in the sample for  "index dynamically for different products"

Please share a better sample if possible

I think you need to use rank

For Rank Refer these links - Check for sub Category rank
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns