Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

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)

 

 

Need Help

6 Replies

  • AntonioHR's avatar
    AntonioHR
    Icon for Helper I rankHelper I
    4 years ago

    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.

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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)