Need help with filtering latest date by projectcode

[kakashi-jk3]

How can I filter latestdate by projectCode?

Here is my table:

I want to filter latest date, so it only show the latest report grouped by each project code. But I want to display all the columns (except status and id) (all the information from the table), and not only projectCode and latestdate which I got to work.

I\ve tried many methods, but none of them really worked out (Got error)

Is Latest = if(Table1[Date] = Table1[MaxDate], "Latest", "")

Im using powerBi Desktop

Best regards

[v-yulgu-msft · Accepted solution]

Hi @kakashi-jk3,

You could create a new calculated table which has been filtered based on some syntax using formula like below: (Suppose source table is named as 'projecttable')

Filter latest date rows =
CALCULATETABLE (
    projecttable,
    FILTER (
        projecttable,
        projecttable[CreatedDate]
            = CALCULATE (
                MAX ( projecttable[CreatedDate] ),
                ALLEXCEPT ( projecttable, projecttable[ProjectCode] )
            )
    )
)

Above DAX will return all the columns from the table, including [status] and [id]. If you only want to return some specific columns in result table, you could use SELECTCOLUMNS to specify those columns you need.

Filter latest date rows =
SELECTCOLUMNS (
    CALCULATETABLE (
        projecttable,
        FILTER (
            projecttable,
            projecttable[CreatedDate]
                = CALCULATE (
                    MAX ( projecttable[CreatedDate] ),
                    ALLEXCEPT ( projecttable, projecttable[ProjectCode] )
                )
        )
    ),
    "projectcode", [ProjectCode],
    "CreateDate", [CreatedDate],
    "etcHours", [etcHours]
)

Best regards,
Yuliana Gu

[kakashi-jk3]

Thanks alot @v-yulgu-msft, I managed to make it work with my own code. 

I realized that the problem my code didn't work is because I didnt use import mode, but rather direct query.

The next filtering I need to do is like:

For example, I got columns Margin , adjusted margin, EAC timer and adjusted timebudget.

Now I want to filter and say: Display the reports for the projectcode if reports has "Margin is less than adjusted margin".

Display reports where EAC is X% larger than adjusted timebudget.

How can I do this? Do you know if regular SQL query works in the DAX?

From the answer you gave me, now it will output all the latest reports.

But I'm also using a slicer tool, which gives me the oppurtunity to choose which projects I want to display the reports for.

I have set by default that if no project is selected in the slicer, we should display all reports for all the projects. But if I'm choosing a project in the slider, I only want to display the latest report as mentioned, is it a way to do this?

Another problem now is with the live connection from direct query, cus I needed to change to import to get to create a new table, but now I just realized in powerbi.microsoft.com, whenever I update the database from Azure SQL it doesnt update there as it used to be.

[v-yulgu-msft]

Hi @kakashi-jk3,

Would you please post your new questions as a new forum thread so that more community members can view it and you would get response more quickly? Also, please provide sample data with your post so that it can help others better understand your requirement and reproduce same scenario in their environment.

Thanks,
Yuliana Gu

[kakashi-jk3]

Bump