CALCULATE AVERAGE ACCUMULATE PER MONTH WITH COLUMNS RULES

[fabiolamelo]

Hi everyone,

I`m trying to find a solution to calculate the average of the accumulative stock by month.

I have one transaction table that gives me the following information:

Tran Date Qty Stock Type DRCR TRAN_SRC
31/08/2020 1,348.00 PEN 1 FA
3/09/2020 3,060.00 PEN 1 LB
8/09/2020 2,250.00 PEN 1 LB
12/09/2020 2,250.00 PEN 1 LB
16/09/2020 4,500.00 PEN 1 LB
29/09/2020 4,000.00 PEN 1 LB
3/10/2020 3,000.00 PEN 1 LB
12/10/2020 4,000.00 PEN 1 LB
22/10/2020 4,000.00 PEN 1 LB
2/11/2020 4,000.00 PEN 1 LB
12/11/2020 4,000.00 PEN 1 LB
19/11/2020 4,000.00 PEN 1 LB
26/11/2020 4,000.00 PEN 1 LB

If column DRCR is equal 1 and TRAN_SRC is different than "AP",
I need to find a way to consider the following rules:

1 - calculate the total accumulative qty by month and stock type

2 - Then the total monthly average by stock type

So the result that I expected is the following below

Month                  | Qty Sum |   Average Qty Year | Average Qty last 2 month( nov / oct) | Average Qty last 3 month(nov / oc/ sept)
november           | 16,000.00 |                                 | 13,500.00                                       |            14,353.33
oct                       | 11,000.00 |
september        | 16,060.00|
august                | 1,348.00 |    11,102.00 |

Qty Sum Average Qty Year Average Qty last 2 month( nov / oct) Average Qty last 3 month(nov / oc/ sept)
november 16,000.00 13,500.00 14,353.33
oct 11,000.00
september 16,060.00
august 1,348.00 11,102.00

 I`ve tried to use the following logic but I don't get the right result.

SUMMARIZECOLUMNS(
table[STOCK_CODE],
table_DATE[Year],
table_DATE[month],
table[QTY],
"Qty_Summary",
VAR P01 =
CALCULATE (
SUM(table[QTY]),
FILTER (
table,
table[DRCR] = "01"
&& table[TRAN_SRC] <> "AP"
)
)
VAR Result = P01
RETURN
Result

)

Can you please help me?

[Powerbiiiiii90]

Hi everyone,

Despite so many time of trying I yet get the result as wanted. Can do me favour on below result needed ?

1) Stock in Whs today

2) Average consumption for past 3 month

Entry No	Entry Type	Item	Posting Date	Created Date	Quantity	Ownership
19263074	Sales	CSOYA	2/1/2024	2/1/2024 3:45	-58	A
19263127	Sales	CSOYA	2/2/2024	2/1/2024 3:46	-33	A
19263281	Sales	CSOYA	2/1/2024	2/1/2024 3:48	-18	A
19300901	Sales	WATER	3/2/2024	3/1/2024 3:02	-35	A
19300911	Sales	CSOYA	3/1/2024	3/1/2024 3:04	-36	A
19301049	Sales	CSOYA	3/1/2024	3/1/2024 3:06	-4	A
19301095	Sales	WATER	3/1/2024	3/1/2024 3:08	-9	A
19341135	Sales	CSOYA	4/2/2024	4/1/2024 2:32	-4	B
19341159	Sales	WATER	4/1/2024	4/1/2024 2:33	-9	B
19341371	Sales	WATER	4/1/2024	4/1/2024 2:51	-3	B
19341411	Sales	WATER	4/2/2024	4/1/2024 2:53	-3	B
19341857	Sales	CSOYA	4/1/2024	4/1/2024 3:03	-22	B
19341869	Sales	CSOYA	3/1/2024	4/1/2024 3:04	-3	A
19342266	Sales	WATER	4/2/2024	4/1/2024 3:35	-36	A
19342309	Sales	WATER	4/1/2024	4/1/2024 3:36	-7	A
19342386	Sales	CSOYA	4/1/2024	4/1/2024 3:38	-20	A
19378986	Sales	CSOYA	5/1/2024	5/1/2024 4:29	-7	B
19379035	Sales	WATER	5/2/2024	5/1/2024 4:30	-15	A
19379067	Sales	CSOYA	5/2/2024	5/1/2024 4:31	-15	A
19379098	Sales	WATER	5/1/2024	5/1/2024 4:33	-54	A
19379126	Sales	CSOYA	5/1/2024	5/1/2024 4:34	-26	A

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Winter_king94]

@Ashish_Mathur do you by chance still have this PBI file? The PBI image you shared looks exactly like what I am trying to do and am curious how you accomplished this.

[Ashish_Excel]

Hi,

No, i do not but i can still help you.  Share some data to work with, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[fabiolamelo]

Thanks @Ashish_Mathur  your solution helped me a lot. 

[Ashish_Mathur]

You are welcome.

[Anonymous]

@fabiolamelo 
You can just create the following measure for accumulative sum with filters, the [month] in bold must be month number not name.

Measure = CALCULATE ( SUM(table[QTY]), FILTER (table, table[DRCR] = "01" && table[TRAN_SRC] <> "AP"), FILTER(ALL(table), [Month]<=MAX([Month]))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.