Calculating MAX measure value over a year.

[marcura]

Hello community,

Im trying to get the highest value over the year, in this example is 66,28%.

The time axis (x) is: 'Tempo'[AnoMesNome]
The percentage axis (y) is: [% OEE]

The [% OEE] measure syntax follow bellow,
% OEE = [% Performance] *  [% Disponibilidade] * [% Qualidade]

I need to get the MAX of [% OEE] over the year, the report have a year dropdown filter ('Tempo'[Ano]).

I've tested this measure and worked, but i've manually inputed the Time "2018/08" :

Test Measure = 
VAR tempTable = 
UNION(
    ROW("Value", [% Performance]),
    ROW("Value", [% Disponibilidade]),
    ROW("Value", [% Qualidade]))
RETURN

CALCULATE(MAXX(tempTable,[% Performance] * [% Disponibilidade] * [% Qualidade]),FORMAT('Eventos OEE'[data inicio id format],"YYYY/MM") = "2018/08")

 This results in:

 I need this value return, without data manually inputed.

Thanks in advance....

[calerof]

@marcura ,

Without data provided, my guess is the simplest, you could just use:

MaxOEE = MAX(Table[%OEE])

That gives you the maximum value. Have you tried it? You don't have to recalculate the OEE again to find the maximum value.

If you want to show the month in a card visual, you could insert this measure in it:

MonthWithMaxOEE = 
FORMAT(
CALCULATE(
    LASTDATE('Calendar'[Date]),
    FILTER(
      Table,
      Table[%OEE] = MAX(Table[%OEE])
    )
), "MMM-YYYY")

That gives you Aug-2018.

See here:

If it didn't help let me know.

Regards,

Fernando

[marcura]

Hello Fernando, i don't have de OEE measure in a column, so i can't reference 

MAX('Eventos OEE'[%OEE]).

[marcura]

Hello Calerof, thanks for the response.

The [% OEE] is a measure, so it can't be a MAX parameter.

Do you have another way of doing the MAX?

Thank you!!!!!

[calerof]

Hi @marcura ,

Let's have table as your data called OEE:

Now we don't use the implicit measures for each column, instead we create explicit ones to compute OEE, like this:

AvailabilityTotal = SUM(OEE[Availability])
PerformanceTotal = SUM(OEE[Performance])
QualityTotal = SUM(OEE[Quality])
OEE% = OEE[PerformanceTotal] * OEE[AvailabilityTotal] * OEE[QualityTotal]

Now, the maximum value for OEE is calculated with this measure:

MaxOEE% = MAXX(VALUES(OEE[Date]), OEE[OEE%])

And for the month we use this one:

MonthWithMaxOEE% = 
VAR myMax = [MaxOEE%]
RETURN
FORMAT(
    MAXX(FILTER(ALL(OEE),[OEE%]=myMax),[Date]),
    "MMM-YYYY"
)

The result is this:

Hope it helps.

Regards,

Fernando

If it helped, you could like the post and mark it as answer.

[marcura]

That helped me a lot !!! Thank you... 🙂

I still have a question, i tried to create the MonthWithMaxOEE:

MonthWithMaxOEE

With, this code:

VAR myMax = _MaxOEE%
RETURN
FORMAT(
     MAXX(FILTER(ALL('Eventos OEE'),[% OEE]=myMax),'Eventos OEE'[data inicio id format]),
    "MMM-YYYY"
) 

 And that returns me blank, i've tried other dates (equal to the one used MaxOEE Measure) but, returns me blank too. 

My _MaxOEE% measure:

MAXX(VALUES('Eventos OEE'[data inicio id format]),[% OEE])

Both using [data inicio id format] date column, of the Eventos OEE table.
The other measures: AvailabilityTotal, PerformanceTotal and QualityTotal also references to the Eventos OEE table.
There are no filters in this PBI that im testing:

Returns:

What do you think that can be affecting the MonthWithMaxOEE%? 

Thanks you soo much

[calerof]

1. Do you have a Calendar table?

2. Show a sample of your data.

[marcura]

Hello, the strange thing is, in the MonthWithMaxOEE measure:

When i compare with myMax variable, it returns me blank, when i compare with MaxOEE measure, it returns me the latest date value in my dataset (2020/07).

[calerof]

Hi @marcura ,

It seems to be a problem with the PBI Community portal; I received an email with your previous comments but they don't appear here. Could you include your pbix file please? You can do that with a public folder in Dropbox or with OneDrive.

Regards,

Fernando