Remove last day production of each category

[Anonymous]

Hello everyone,
I want to make an average per day of the production of each Item then sum it, BUT I want to remove te last day of production per item.

I Want to remove the last day of the average because if i don´t do so i will make me average go under the real number.

 Here under I have a simple example of it (The red numbers are the numbers I want removed.

Date	Product 1	Product 2	Product 3
01/01/2018	5000	100	50
02/01/2018	5000	100	50
03/01/2018	5000	100	25
04/01/2018	5000	100
05/01/2018	5000	100
06/01/2018	5000	100
07/01/2018	2500	100
08/01/2018		50
09/01/2018				Average
Average	5000	100	50	1716,667

The table in the database that I have presents 3 colums, Product / Date / Value

Product	Date	Value
Product 1	01/01/2018	XXX
Product 2	02/01/2018	XXX
Product 3	03/01/2018	XXX
Product 1	04/01/2018	XXX
Product 2	05/01/2018	XXX
Product 3	06/01/2018	XXX
Product 1	07/01/2018	XXX
Product 2	08/01/2018	XXX
Product 3	09/01/2018	XXX
Product 3	10/01/2018	XXX

Thanks in regards

[Anonymous]

Hello everyone, thanks for the answer.

I tried to make the following DAX Formula:

AVERAGEX(
 KEEPFILTERS(VALUES('TABLE'[Date]);
 CALCULATE(MEASURE)

And it worked really fine, the problem is that doesnt filter the last day....

[Greg_Deckler]

Did you try my technique to get rid of the last day in your calculation?

[Anonymous]

Greg!
I did your technique, and almost worked!
The problem is, is making a average of each line instead of the average per day.

In the example below, it should give me on day 01 the value of 50, and day 2 a number of 100.

I think is something with KEEPFILTERS, but I can´t get it right...

Date	Product	Value
01/01/2018	Product 1	25
01/01/2018	Product 1	25
01/02/2018	Product 1	25
01/02/2018	Product 1	25
01/02/2018	Product 1	25
01/02/2018	Product 1	25

Thanks!

[v-piga-msft]

Hi @Anonymous,

Assuming that you have the data sample like below.

Please filter rows like below in Query Editor and then apply and close.

Then you could create a calculated column with the formula like this.

Column =
VAR a =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Product] ) )
RETURN
    IF ( 'Table'[Date] = a, BLANK (), 'Table'[Value] )

Here is the output.

Best  Regards,

Cherry

[Greg_Deckler]

You should be able to do something like:

Measure = 
VAR __product = MAX([Product])
VAR __table = FILTER('Table',[Product]=__product)
VAR __max = MAXX(__table,[Date])
VAR __table1 = FILTER(__table,[Date]<>__max)
RETURN
AVERAGEX(__table1,[Value])

This assumes that the data in Power BI is in the 2nd example posted.