Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
diogobraga2
Helper IV
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
Ashish_Mathur
Super User
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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-shex-msft
Community Support
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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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