Calculation to return value based on maximum date and maximum year

[diogobraga2]

The goal is to create a dynamic calculation that will show the 'Returning Rate' for the maximum 'Date Adj' for the maxium 'Academic Year' per school per grade. 

Based on this dataset the 'Returning Rate MAX' calculation should return the rates for maximum 'Academic Year' is 2020-2021 and the maxium 'Data Adj' is 08/24.

Refer to the pbix on this link. Thanks for you help! 

[Ashish_Mathur · Accepted solution]

Hi,

To a matrix visual, i dragged the School Code anf Grade Level and then wrote these measures

Measure = MAX('result (5)'[Academic Year])

Measure 2 = MAX('result (5)'[Date])

Hope this helps.

[v-shex-msft]

Hi @diogobraga2,

If you mean to get the static value based on current table records groups, this is possible. You can try to use following calculated column formula:

Academic Year MAX =
CALCULATE (
    MAX ( 'result (5)'[Academic Year] ),
    FILTER (
        'result (5)',
        [School Code] = EARLIER ( 'result (5)'[School Code] )
            && [Grade Number] = EARLIER ( 'result (5)'[Grade Number] )
    )
)

Date MAX =
CALCULATE (
    MAX ( 'result (5)'[Date Adj] ),
    FILTER (
        'result (5)',
        [School Code] = EARLIER ( 'result (5)'[School Code] )
            && [Grade Number] = EARLIER ( 'result (5)'[Grade Number] )
    )
)

If you mean to create dynamic column fields based on filters, this is impossible.

Currently, you can't create a dynamic calculated column/table based filters. They are stored in different data levels and parent level data model(calculated column/table) not respond with the child level(measure, visual, filter, slicer) effects.

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> virtual table of data view(measure, visual, filter, slicer)
Regards,

Xiaoxin Sheng