Create a Index comparison based on first row selected

Question

Dear Colleagues, I'm looking for a simple solution to use the DAX measures to get the index based on the first value.&nbsp;The example is below with the Index value targeted as results. It needs to change dynamicaly depending on the Date.&nbsp;I have been trying firstnonblank but it doesnt give the results when the Dates are visible.&nbsp;It would be great if somebody can assist, the PBI file is available in this link =&nbsp;PBI Link&nbsp;DateValueIndexJan-19150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100Feb-19160&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 107Mar-19165&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 110Apr-19177&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 118May-19178&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 119Jun-19178&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 119Jul-19200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 133Aug-19220&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 147Sep-19224&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 149Oct-19190&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 127Nov-19180&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 120Dec-19190&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 127Jan-20220&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 147Feb-20200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 133Mar-20200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 133Apr-20230&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 153May-20240&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 160Jun-20250&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 167Jul-20260&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 173Aug-20220&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 147

v-lili6-msft · Accepted Answer

hi,&nbsp;leandroduarte&nbsp;
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/
&nbsp;
Then you could use RANKX Function to create a measure
Measure = RANKX(ALLSELECTED(Table1),CALCULATE(SUM(Table1[Amount])),,ASC,Dense)
Result:

Best Regards,
Lin
&nbsp;
&nbsp;

Forum Discussion

Create a Index comparison based on first row selected

1 Reply

Related content

BI Tools Comparison

Comparison Chart

Create comparison on same data source

Can i use a measure selected in a slicer (created with field parameters) to create a rank?

Comparison Table

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