Return fields based on second highest value in RANK column - please HELP !

[vbabka]

Hello everyone,

I tried to search multiple forums but i couldnt figure it out not even after reading and trying multiple options.

Description:

I have report showing attendance data based on Year_Month and measures. Year_Month i am taking from xls filenames (HC_Year_Month.xlsx).

In all KPI visuals i am showing values for actual Year_Month in default, or the selected Year_Month(s) in slicer.

But know, I need to show only on one page/one visual values for actual Year_Month, but on the rest i need to show values for 2nd newest/highest Year_Month value.

I created calculated RANK column, but i cannot figure out how to put in in the visuals.

Now I am  using below mesures to show visuals for Actual Month:

1) MaxYM = max(Headcount[Year_Month])

2) List of Year_Month Values - using for Title of KPI (Text + Year_Month i am showing values for)

List of Year_Month values = 
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Headcount'[Year_Month])
VAR __MAX_VALUES_TO_SHOW = 7
RETURN
IF(ISFILTERED(Headcount[Year_Month]),

	IF(
		__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
		CONCATENATE(
               
			CONCATENATEX(
				TOPN(
					__MAX_VALUES_TO_SHOW,
					VALUES('Headcount'[Year_Month]),
					'Headcount'[Year_Month],
					ASC
				),
				'Headcount'[Year_Month],
				", ",
				'Headcount'[Year_Month],
				ASC
			),
			", etc."
		),
        
		CONCATENATEX(
			VALUES('Headcount'[Year_Month]),
			'Headcount'[Year_Month],
			", ",
			'Headcount'[Year_Month],
			ASC
		)
	),Headcount[MaxYM])

 3) Absenteeism_Rate = one of measures showing in KPI visual

Absenteeism_Rate = 
VAR MaxDate =
    CALCULATE (
        MAX ( Headcount[Year_Month] ),
        
        ALLSELECTED ( Headcount[Year_Month] )
    )
RETURN
    IF (
        ISFILTERED ( Headcount[Year_Month] ),
        Absence[Absenteeism Rate],
        CALCULATE (
            Absence[Absenteeism Rate],
            FILTER ( Headcount, Headcount[Year_Month] = MaxDate )
        )
    )

 4) Calculated column for Rank Year_Month

= Table.AddRankColumn (#"Merged Columns1","RankYM",{"Year_Month"},[RankKind=RankKind.Dense])

Can you help how to add Measure like if RankYM = 2, then return Year_Month, so i can create duplicated measures like "2NDAbsenteeism_rate" and "2ndList of Year Month Values" showing Year Month with Rank 2 in some of the visuals ?

Thank You very much for any help or idea.

Vojtech Babka

[amitchandak]

@vbabka , it is better to use the new index function

example

Top 2 = CALCULATE([Net], INDEX(1,SUMMARIZE(ALLSELECTED(sales),'Date'[Month Year],"_1", [Net]),ORDERBY([_1],DESC)))

refer

Continue to explore Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U