Maximum count every single month for each unique customer

[okiedokie2017]

Hello, 

I have been stuck on this issue for ages and would like someone to take a look at it. 

Basically, I have a table like this: 

Configured	Original Timestamp	users.customers
1	13/02/2018 20:25	A
4	14/02/2018 20:25	A
6	15/02/2018 20:25	B
12	16/02/2018 20:25	A
5	17/02/2018 20:25	B
7	18/02/2018 20:25	E
25	19/02/2018 20:25	C

What I am trying to achieve is this: 

User.customers	Max Configured per Month	Date
A	12	16/02/2018
B	6	15/02/2018
C	25	19/02/2018

At the moment, the dataset is from Jan 2017 till Feb 2018 so I would like to get the maximum of "configured" number every month for each unique customers. 

This is what  I have so far but it is not working. 

Table =
VAR tblMonths =
SUMMARIZE(
ADDCOLUMNS(
CALENDAR("2016-11-01","2018-03-01"),
"Month",DATE(YEAR([original_timestamp]),MONTH([original_timestamp]),1)
),[Month])

RETURN
ADDCOLUMNS(
GENERATE(
VALUES(system_monitored[users.customer]),
tblMonths
)
,"Order Value" ,
CALCULATE(
MAX(system_monitored[configured]),
FILTER(system_monitored,
system_monitored[users.customer] = EARLIER(system_monitored[users.customer]) && system_monitored[original_timestamp] = EARLIER([Month])
)
))

[v-ljerr-msft · Accepted solution]

Hi @okiedokie2017,

If I understand you correctly, you should be able to follow steps below to get the expected result in your scenario.

1. Use the formula below to add a YearMonth column in table 'system_monitored'.

YearMonth = YEAR(system_monitored[Original Timestamp])*100+MONTH(system_monitored[Original Timestamp])

2. Then you should be able to use the formula below to create the expected table. 

Table = 
SUMMARIZE (
    system_monitored,
    system_monitored[YearMonth],
    system_monitored[users.customers ],
    "Max Configured per Month", CALCULATE ( MAX ( system_monitored[Configured] ) ),
    "Date",
    VAR abc =
        CALCULATE ( MAX ( system_monitored[Configured] ) )
    RETURN
        CALCULATE (
            MAX ( system_monitored[Original Timestamp] ),
            system_monitored[Configured] = abc
        )
)

Here is the sample pbix file for your reference. 

Regards

[okiedokie2017]

WOw thanks alot. 

This is perfect