4Qrolling average

[Anonymous]

Hello,

I'm trying to calculate a 4Q rolling average based on my selling price, in the table 'Sell', the selling date are from the 09/01/2018 until today. 

I looking for the average of selling price on a quarterly basis and by supplier, but I want to exclude the selling statuts "Rejected", and the type "Out".

I have used this formula, 4QRoll = CALCULATE(sum('Sell'[Price]),ALLEXCEPT('Sell','Sell'[Supplier]),DATESBETWEEN('Sell'[Selling Date],'Sell'[Selling Date]-365, 'Sell'[Selling Date])), but this is giving to me a yearly roll and it's not excluding the statuts "rejected" and "out". 

I'm in a dead end, could you support please ? 

Best regards, 

Julien

[Anonymous]

Hello, 

I want to create a line shart, with the 4Q Rolling average on Y-Axis (one line for each supplier) and the time in Year and quarter on the X-Axis. 

Bellow a data sample, I'm limited in number of Character

Supplier	Selling Date	Selling Status	Type	Price
Erita	09/01/2018	Closed	Local	3,2
Camila	09/01/2018	In progress	Local	1,38
Erita	09/01/2018	Closed	External	6,8
Camila	09/01/2018	In progress	Local	4,62
Camila	09/01/2018	In progress	Local	4,18
FIL	11/01/2018	Closed	Out	30
Chiwe	15/01/2018	Closed	Local	4,32
Chiwe	15/01/2018	In progress	Local	7,2
FIL	16/01/2018	Closed	Local	1,61
FIL	16/01/2018	Rejected	External	18,25
DLO product	16/01/2018	Closed	Local	2,9
Visions	18/01/2018	Closed	Local	0,64
Gotor	22/01/2018	In progress	Local	5
Voleat	22/01/2018	Closed	Local	5,2
MG-TC	24/01/2018	Closed	Out	4,8
Camila	24/01/2018	In progress	Local	5,06
Camila	25/01/2018	In progress	Local	5,06
Camila	25/01/2018	Closed	Local	5,06
DEC	25/01/2018	In progress	External	10
Erita	25/01/2018	In progress	Local	5
Erita	28/01/2018	Rejected	Out	10
Wito	29/01/2018	In progress	External	2,4
Wito	29/01/2018	Rejected	Local	0,75
Camila	29/01/2018	In progress	Out	3,08
Gotor	29/01/2018	Closed	Local	10
Camila	29/01/2018	Closed	Out	6,16
Camila	29/01/2018	In progress	Local	5,06
Camila	29/01/2018	Closed	Local	5,06
Voleat	01/02/2018	Closed	Local	4,5
Suisoa	02/02/2018	Closed	Local	2,7
FIL	05/02/2018	Closed	Local	36
Erita	11/02/2018	In progress	Local	5
Erita	13/02/2018	Closed	Local	4,8
Chiwe	14/02/2018	In progress	Local	2
Wito	14/02/2018	In progress	Local	4,8
Wito	14/02/2018	Rejected	Local	3
TRO	15/02/2018	Closed	Local	20
Chiwe	19/02/2018	In progress	Local	1,92
DLO product	21/02/2018	Closed	Local	3,6
Suisoa	21/02/2018	Closed	Local	10,12
Voleat	21/02/2018	Rejected	Local	5
Voleat	21/02/2018	Closed	Local	5,7
Erita	22/02/2018	In progress	Local	5
Erita	02/03/2018	Closed	Local	2,2
Erita	02/03/2018	Closed	Local	2,6
Erita	02/03/2018	Rejected	Local	10
Gotor	08/03/2018	In progress	Local	2,4
TRO	08/03/2018	In progress	Local	5
Voleat	08/03/2018	Closed	Local	7,9
SEwind	09/03/2018	Rejected	Local	8,5
Suisoa	12/03/2018	Closed	Local	2,99
Suzlon	12/03/2018	In progress	Local	7,56
Voleat	12/03/2018	Closed	Local	6,08
Voleat	12/03/2018	Closed	Local	1,08
Suisoa	13/03/2018	Closed	Local	2,38
Chiwe	13/03/2018	Closed	Local	1,2
Suisoa	14/03/2018	Closed	Local	22,6
Suzlon	19/03/2018	In progress	Local	49,98
DLO product	20/03/2018	Closed	Local	1,82
Visions	22/03/2018	Closed	Local	0,96
Visions	23/03/2018	In progress	Local	0,72
Voleat	26/03/2018	Closed	Local	3,08
Voleat	26/03/2018	Closed	Local	1,98
Chiwe	26/03/2018	Closed	Local	2,16
DLO product	28/03/2018	Closed	Local	0,63
DLO product	28/03/2018	Closed	Local	2
DLO product	28/03/2018	Closed	Local	0,96
Voleat	28/03/2018	Closed	Local	13,5
DLO product	28/03/2018	Closed	Local	0,36
DLO product	28/03/2018	Closed	Local	1,28
Chiwe	29/03/2018	Closed	Local	2,16
Voleat	29/03/2018	Closed	Local	4,5
Voleat	29/03/2018	Closed	Local	2,07
XEMC	30/03/2018	In progress	Local	4,8
Gotor	30/03/2018	In progress	Local	4,8
Gotor	30/03/2018	In progress	Local	5
Erita	30/03/2018	In progress	Local	1
Voleat	30/03/2018	Closed	Local	15,9
Suisoa	31/03/2018	In progress	Local	7,3
Suisoa	31/03/2018	Closed	Local	0,34
Suisoa	31/03/2018	Closed	Local	0,2
Suisoa	31/03/2018	Closed	Local	0,34
Suisoa	31/03/2018	Closed	Local	0,68
Suisoa	31/03/2018	Closed	Local	1,03
Suisoa	31/03/2018	Closed	Local	0,2
Suisoa	31/03/2018	Closed	Local	0,41
Suzlon	31/03/2018	In progress	Local	5,1
Suzlon	31/03/2018	In progress	Local	28,5
DLO product	31/03/2018	Closed	Local	10
DLO product	31/03/2018	Closed	Local	7,48
DLO product	31/03/2018	Closed	Local	19,4
DLO product	31/03/2018	Closed	Local	1,15
DLO product	31/03/2018	In progress	Local	30
DLO product	31/03/2018	Closed	Local	3,1
DLO product	31/03/2018	Rejected	Local	6,5625
DLO product	31/03/2018	Closed	Local	7,6
DLO product	31/03/2018	Closed	Local	9,28
DLO product	31/03/2018	Closed	Local	1,6
DLO product	31/03/2018	Closed	Local	0,8
DLO product	31/03/2018	Closed	Local	1
DLO product	31/03/2018	Closed	Local	15,5
DLO product	31/03/2018	Closed	Local	6,3
Chiwe	31/03/2018	Closed	Local	12,48
Chiwe	31/03/2018	In progress	Local	5,4
Chiwe	31/03/2018	Closed	Local	11,34
Chiwe	31/03/2018	In progress	Local	29
Chiwe	31/03/2018	In progress	Local	13,27
FIL	31/03/2018	In progress	Local	149,04
DLO product	31/03/2018	In progress	Local	5,8
Suisoa	31/03/2018	In progress	Local	4,5
Suisoa	31/03/2018	In progress	Local	7,06
DLO product	31/03/2018	In progress	Local	8,2
DLO product	31/03/2018	In progress	Local	10,72
DLO product	31/03/2018	In progress	Local	52,1875
DLO product	31/03/2018	In progress	Local	36,17
Voleat	31/03/2018	Closed	Local	5,05
Voleat	31/03/2018	Closed	Local	20,9
Voleat	31/03/2018	Closed	Local	13,8
Voleat	31/03/2018	Closed	Local	9,2
Voleat	31/03/2018	In progress	Local	6,55
Voleat	31/03/2018	In progress	Local	35,71
Voleat	31/03/2018	In progress	Local	3
Erita	02/04/2018	In progress	Local	4,6
Erita	02/04/2018	Rejected	Local	2
Camila	02/04/2018	In progress	Local	3,3
Gotor	02/04/2018	Closed	Local	10
Camila	02/04/2018	Closed	Local	4,62
HEAG	03/04/2018	In progress	Local	10
TRO	06/04/2018	Closed	Local	10
Erita	08/04/2018	Closed	Local	5,06
FIL	09/04/2018	Closed	Local	10
Wito	13/04/2018	In progress	Local	6
Erita	13/04/2018	Closed	Local	10
DLO product	16/04/2018	In progress	Local	16,6
Gotor	17/04/2018	In progress	Local	5
Erita	17/04/2018	In progress	Local	10
Camila	23/04/2018	Closed	Local	5
Chiwe	24/04/2018	Closed	Local	12,6
Chiwe	24/04/2018	Closed	Local	2,1
Camila	26/04/2018	In progress	Local	4,18
FIL	26/04/2018	Closed	Local	9,86
FIL	26/04/2018	Closed	Local	20,14
Chiwe	03/05/2018	Closed	Local	6
Voleat	05/05/2018	Rejected	Local	4,8
Erita	07/05/2018	In progress	Local	4,6
Camila	07/05/2018	In progress	Local	11
Erita	07/05/2018	Closed	Local	10
Erita	07/05/2018	Closed	Local	20
Camila	07/05/2018	Closed	Local	25,08
Camila	08/05/2018	In progress	Local	5,06
FIL	08/05/2018	Closed	Local	47
DLO product	08/05/2018	Closed	Local	19,95
Erita	08/05/2018	Closed	Local	5
FIL	09/05/2018	Closed	Local	1,09
FIL	09/05/2018	Closed	Local	1,09
Erita	10/05/2018	Closed	Local	10
Erita	10/05/2018	In progress	Local	5
Erita	10/05/2018	In progress	Local	10
Erita	10/05/2018	Rejected	Local	3,6
Voleat	10/05/2018	Closed	Local	17,7

[Fowmy · Accepted solution]

@Anonymous 

I created a file based on your data and added a calendar table as well. Please create this measure.

4Q Rolling Avg = 

CALCULATE(
    AVERAGEX(
        ADDCOLUMNS(
            DATESINPERIOD(
                'Dates'[Date],
                MAX('Dates'[Date]),
                -4,
                QUARTER
            ),
            "Price", CALCULATE(AVERAGE('Sell'[Price]))
        ),
        [Price]
    ),
    Sell[Selling Status] <> "Rejected",
    Sell[Type] <> "Out"
)

[Fowmy]

@Anonymous 

I've got a question, how are you going to visualize this result? Could you share an expected out put layout ( What is in rows, column and values? May be result based on your sample data. You may copy and paste in your reply rather than pasting as image which I cannot use to test the working.