Calculated column with column = measure yields different result than column = hardcoded value

[brst]

Hello,

I would like to add calculated columns indicating if a row contains the latest or second latest value in the file_date column.

This works well with the latest value, but does not with the second latest value - the result is always zero.

The second latest value calculation works when the "dynamic" second latest value is replaced with the hardcoded value of itself. The value calculated by the measure seems to be right, as the table in the second picture shows. The data types are the same for updates_most_recent, updates_second_most_recent.

Updates_second_most_recent is calculated the way it is to avoid a circular reference as described in this forum.

Thanks for your help and enjoy your weekend 😀

# measures, in measures table
updates_most_recent = max(updates_engagement_bei_updates[file_date])

updates_second_most_recent = var thisdate= calculate(max(updates_engagement_bei_updates[file_date]), ALLEXCEPT(updates_engagement_bei_updates,updates_engagement_bei_updates[file_date]))
var seconddate = CALCULATE(max(updates_engagement_bei_updates[file_date]),updates_engagement_bei_updates[file_date]<thisdate, ALLEXCEPT(updates_engagement_bei_updates,updates_engagement_bei_updates[file_date]))
return  seconddate

# calculated columns
is_latest = if(max(updates_engagement_bei_updates[file_date])=[updates_most_recent],1,0)
is_latest_hard = if(updates_engagement_bei_updates[file_date]=date(2022,3,9),1,0)
is_second_latest = if(max(updates_engagement_bei_updates[file_date])=('measure'[updates_second_most_recent]),1,0)
is_second_latest_hard = if((updates_engagement_bei_updates[file_date])=date(2022,2,9),1,0)

[v-chenwuz-msft]

Hi @brst ,

Measure result is depend on the filter context, by for calculate column, you can consider it has filter context also, but its context is the whole table and won't be changed.

And you can test these columns in one table.

column1 = 'measure'[updates_second_most_recent]

column2 = date(2022,2,9)

Then try this code:

is_second_last_column =
VAR _topn =
    TOPN ( 2, VALUES ( 'table'[file_date] ), [file_date], DESC )
RETURN
    MINX ( _topn, [file_date] )

Best Regards

Community Support Team _ chenwu zhu

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

[brst]

One more question:

rankx result is wrong - even though it's not a tie

Threads on rankx with datetime columns like Solved: Rank based on date for each individual user - Microsoft Power BI Community unfortunatelly did not work

[AntonioHR]

mmm try RANKX just with table and date, It works for me. 

rank = RANKX(calc_table_updates, file_date)

[brst]

Hmm thats how I did it - Re: Calculated column with column = measure yields... - Microsoft Power BI Community

rank = RANKX(calc_table_updates, [file_date],,,Skip)

[brst]

Thank you, that works

Just for future reference, here are some details for the suggested solution by @AntonioHR 

# calculated table
calc_table_updates = distinct(SELECTCOLUMNS(updates_engagement_bei_updates,"file_date", [file_date]))

# rank column of calculated table
rank = RANKX(calc_table_updates, [file_date],,,Skip)

[AntonioHR]

Hello, 

I'm not sure if I have understand it correctly, but you could try creating a table (DISTINCT dates) and using RANKX on it. 1 and 2 will be the lastest and second lastest, so after you link the two tables using date you can add the calculated column.