Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Need help! Latest date by month DAX

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!

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    7 years ago

    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.

5 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-lid-msft ,

       

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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] .......