DAX Calculate - Filter max date on several columns

[crolland]

My original table is

Project  /  UpdateDate / FiscalYear

A   / 2022-04-01   /    FY2022-2023

B   / 2022-05-01   /     FY2022-2023

A   / 2022-05-01   /     FY2022-2023

A   / 2023-04-01   /      FY2023-2024

I am trying to find the latest update date per fiscal year.

Here is what I tried, with no success

TEST_table = /*FILTER(*/

        ADDCOLUMNS (

            FILTER(Epic_History, Epic_History[Update_FY] = "FY2022-2023"),

            "IfMax", CALCULATE (

                MAX ('JIRA Export'[Custom.Update] ),

                FILTER (Epic_History, Epic_History[Update_FY] = "FY2022-2023"

                &&  (Epic_History[Custom.Custom field (Epic Name)] = EARLIER(Epic_History[Custom.Custom field (Epic Name)])) ) )

            )/*, 

[IfMax] = Epic_History[Custom.Update]

)*/

AND I am still getting IfMax = 2023-04-01 (instead of 2022-05-01) even if  I added a filter (FY2022-2023). 

Need your help. THANKS !!

[crolland]

Great ! Thank you very much for your help 🙂 

[Anonymous]

Hi @crolland ,

Please try below steps:

1. below is my test table

Table:

2. create a measure with below dax formula

Measure =
VAR cur_fy =
    SELECTEDVALUE ( 'Table'[Fiscal Year] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Fiscal Year] = cur_fy )
RETURN
    MAXX ( tmp, [Update Date] )

3. add a table visual with [FiscalYear] column and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

I am confused because you have a Project column there as well.  Clearly show the expected result.

[djurecic · Accepted solution]

Hi @crolland ,

 You could try the following:

Table2 = SUMMARIZE('Table','Table'[Project], 'Table'[FiscalYear], "Last Update",MAX('Table'[UpdateDate]))

Result:

Base table:

Please consider accepting as solution if this has answered the question.