Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@Julien_FR
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"
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
@Julien_FR
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"
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Julien_FR
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.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
109 | |
89 | |
76 | |
66 |
User | Count |
---|---|
126 | |
112 | |
99 | |
82 | |
73 |