Forum Discussion
4Qrolling average
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 o...
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" )
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 |
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"
)