Forum Discussion

kakashi-jk3's avatar
kakashi-jk3
Frequent Visitor
9 years ago
Solved

Need help with filtering latest date by projectcode

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...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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