cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper IV

## Calculation to return value based on maximum date and maximum year

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!

1 ACCEPTED SOLUTION
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
2 REPLIES 2
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors