Need help! Latest date by month DAX

[Anonymous]

I have two columns as below:

Item                     Date

AAA                    1/5/2019

AAA                    1/10/2019

AAA                    1/20/2019

AAA                    2/5/2019

AAA                    2/10/2019

AAA                    3/6/2019

BBB                     1/6/2019

BBB                     1/16/2019

CCC                     1/8/2019

etc.

I would like to see the latest (max) date for each month associates with different item, Ideal result would be as below:

Item                 Date

AAA                1/20/2019

AAA                2/10/2019

AAA                3/6/2019

BBB                 1/16/2019

CCC                 1/8/2019

Thansk a lot for the help!

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can use a measure and a table visual to meet your requirement:

IsLastDay =
IF (
    MAX ( [Date] )
        = MAXX (
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Item] = MAX ( [Item] )
                    && MONTH ( 'Table'[Date] ) = MONTH ( MAX ( [Date] ) )
                    && YEAR ( 'Table'[Date] ) = YEAR ( MAX ( [Date] ) )
            ),
            [Date]
        ),
    "Yes",
    BLANK ()
)

Or we can add a calculated column using following dax:

LastDay =
VAR m =
    MONTH ( [Date] )
VAR y =
    YEAR ( [Date] )
VAR i = [Item]
RETURN
    IF (
        [Date]
            = MAXX (
                FILTER (
                    'Table',
                    'Table'[Item] = i
                        && MONTH ( 'Table'[Date] ) = m
                        && YEAR ( 'Table'[Date] ) = y
                ),
                [Date]
            ),
        "Yes",
        "No"
    )

Or we can create a calculated table to meet your requirement.

LastDatTable =
SELECTCOLUMNS (
    FILTER (
        ADDCOLUMNS (
            'Table',
            "status",
            VAR m =
                MONTH ( [Date] )
            VAR y =
                YEAR ( [Date] )
            VAR i = [Item]
            RETURN
                IF (
                    [Date]
                        = MAXX (
                            FILTER (
                                'Table',
                                'Table'[Item] = i
                                    && MONTH ( 'Table'[Date] ) = m
                                    && YEAR ( 'Table'[Date] ) = y
                            ),
                            [Date]
                        ),
                    "Yes",
                    "No"
                )
        ),
        [status] = "YES"
    ),
    "Item", [Item],
    "Date", [Date]
)

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lid-msft ,

Thanks a lot, it works with my model. I really appreciate that!

[Anonymous]

I think you can just show the 'Latest' using the dropdown on the value. My data is slightly different than yours but it should work.

If this works, please mark this answer as solved so it is easier for others to find. Kudos!

[Anonymous]

Hi Thanks a lot for the help. I think I still need a measure or column, since I will make this as a page level filter/ filter in further measures. 

So my data looks like this, I would like to only focus on the latest date for 10037 BL product, which should be 1/31/2018 for January, and 2/19/2019 for February. Then I will make another measure to calculate $ value based on that date of record.

I tried to make a new measure like this, but have no idea what should I put in the filter part.

End of Month = (MAX(Table1[INVP_Date]),filter(Table1,Table1[Month] .......

[Anonymous]

Ah, I see what you are wanting to do. I am kind of stumped but I did find this similar problem/solution from @CheenuSing 

https://community.powerbi.com/t5/Desktop/DAX-expression-for-Max-Date-data-display-in-Table/td-p/1438...