Create a Index comparison based on first row selected

[leandroduarte]

Dear Colleagues, I'm looking for a simple solution to use the DAX measures to get the index based on the first value.

The example is below with the Index value targeted as results. It needs to change dynamicaly depending on the Date.

I have been trying firstnonblank but it doesnt give the results when the Dates are visible.

It would be great if somebody can assist, the PBI file is available in this link = PBI Link

Date	Value	Index
Jan-19	150	100
Feb-19	160	107
Mar-19	165	110
Apr-19	177	118
May-19	178	119
Jun-19	178	119
Jul-19	200	133
Aug-19	220	147
Sep-19	224	149
Oct-19	190	127
Nov-19	180	120
Dec-19	190	127
Jan-20	220	147
Feb-20	200	133
Mar-20	200	133
Apr-20	230	153
May-20	240	160
Jun-20	250	167
Jul-20	260	173
Aug-20	220	147

[v-lili6-msft · Accepted solution]

hi, @leandroduarte 

First, you should know that calculated column and calculate table can't be affected by any slicer. you could create a measure instead of column.
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Then you could use RANKX Function to create a measure

Measure = RANKX(ALLSELECTED(Table1),CALCULATE(SUM(Table1[Amount])),,ASC,Dense)

Result:

Best Regards,

Lin