Measure to find last Purchased date each month based on Customer

[Anonymous]

Hi Experts

How would you do a summarised table and or a measure and or calculated column to give you the last purchased date each month based on the customer in first column

Customer	Date
abc	01/01/2022
abc	27/01/2022
abc	02/03/2022
abc	22/03/2022
abc	03/06/2022
abc	22/06/2022
ddd	01/01/2022
ddd	27/01/2022
ddd	02/03/2022
ddd	22/03/2022
ddd	03/06/2022
ddd	22/06/2022

[Samarth_18]

Hi @Anonymous ,

You can create a measure like below:-

_measure =
CALCULATE (
    MAX ( 'Table (7)'[Date] ),
    FILTER (
        ALL ( 'Table (7)'[Date] ),
        MONTH ( 'Table (7)'[Date] ) = MONTH ( SELECTEDVALUE ( 'Table (7)'[Date] ) )
            && YEAR ( 'Table (7)'[Date] ) = YEAR ( SELECTEDVALUE ( 'Table (7)'[Date] ) )
    )
)

Output:-

Thanks,

Samarth

[Anonymous]

Hi Samarth - I just want to show the max date for each month - Not all the data.... please..

[Anonymous]

end result

Customer	Date
abc	27/01/2022
abc	22/03/2022
abc	22/06/2022
ddd	27/01/2022
ddd	22/03/2022
ddd	22/06/2022

[Samarth_18 · Accepted solution]

@Anonymous Please create a column with below code:-

Column =
VAR _mon =
    MONTH ( [Date] )
VAR _year =
    YEAR ( [Date] )
RETURN
    CALCULATE (
        MAX ( [Date] ),
        FILTER (
            ALL ( 'Table (7)' ),
            MONTH ( [Date] ) = MONTH ( EARLIER ( 'Table (7)'[Date] ) )
                && YEAR ( [Date] ) = YEAR ( EARLIER ( 'Table (7)'[Date] ) )
                && 'Table (7)'[Customer] = EARLIER ( 'Table (7)'[Customer] )
        )
    )

Now use this column on your visual

Thanks,

Samarth

[Samarth_18]

Hi @Anonymous It would be very helpful if you could share the snapshot of expected output?

[TheoC]

Hi @Anonymous 

Can you try the following as a new table. Apologies, am on phone so can't test syntax. 

Table =
SUMMARIZE (
    'Table',
    'Table'[Customer],
    "MaxDate", MAX ( 'Table'[Date] ) )

[TheoC]

@Anonymous did you try my solution?