cancel
Showing results for
Did you mean:
Regular Visitor

## Maximum count every single month for each unique customer

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(
CALENDAR("2016-11-01","2018-03-01"),
"Month",DATE(YEAR([original_timestamp]),MONTH([original_timestamp]),1)
),[Month])

RETURN
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])
)
))

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Maximum count every single month for each unique customer

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

2 REPLIES 2
Super Contributor

## Re: Maximum count every single month for each unique customer

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

Regular Visitor

## Re: Maximum count every single month for each unique customer

WOw thanks alot.

This is perfect

Announcements

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 162 members 1,769 guests
Recent signins: