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.
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?
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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 |
Hi,
You may download my PBI file from here.
Hope this helps.
You are welcome.
@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.
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 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |