Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
Iam stuck with some calculation of arriving cumulative total.
The following is the data i have.
Plant Id | Material | Req/Availbility Date | Opening Qty | Qty Utilized |
1110 | 400269 | 31-05-2022 | 10,375 | 219 |
1110 | 400269 | 24-06-2022 | 10,375 | -576 |
1110 | 400269 | 24-06-2022 | 10,375 | -856 |
1110 | 400269 | 24-06-2022 | 10,375 | -960 |
1110 | 400269 | 01-07-2022 | 10,375 | -128 |
1110 | 400269 | 01-07-2022 | 10,375 | -1,024 |
1111 | 400269 | 31-05-2022 | 50,000 | -8 |
1111 | 400269 | 24-06-2022 | 50,000 | -4,000 |
1111 | 400269 | 24-06-2022 | 50,000 | 1,000 |
1111 | 400269 | 24-06-2022 | 50,000 | -340 |
1111 | 400269 | 01-07-2022 | 50,000 | -4,577 |
1111 | 400269 | 01-07-2022 | 50,000 | 434 |
I need to calculate the cumulative (measure or column) based on the Plant, Material, and Date.
For example for Plant 1110 and material 40269 the least date is 31-05-2022 and opening inventory is 10,375 and utilized is 219. So for this line my cumulative is 10375 + 219 = 10594.
Next cumulative should be 10594 + (-596) = 10,018 and so on.
When the next combination ofh Plant and MAterial starts i.e., Plant 1111 and material 4000269 againfirst calucaltin should be of the least date 31-05-2022 and cumulative total is 50000 + (-8) = 49992 and second calcualation is 49992 + (-4000) = 45992.
The following is the sample result for your reference.
Plant Id | Material | Req/Availbility Date | Opening Qty | Qty Utilized | Cumulative |
1110 | 400269 | 31-05-2022 | 10,375 | 219 | 10,594 |
1110 | 400269 | 24-06-2022 | 10,375 | -576 | 10,018 |
1110 | 400269 | 24-06-2022 | 10,375 | -856 | 9,162 |
1110 | 400269 | 24-06-2022 | 10,375 | -960 | 8,202 |
1110 | 400269 | 01-07-2022 | 10,375 | -128 | 8,074 |
1110 | 400269 | 01-07-2022 | 10,375 | -1,024 | 7,050 |
1111 | 400269 | 31-05-2022 | 50,000 | -8 | 49,992 |
1111 | 400269 | 24-06-2022 | 50,000 | -4,000 | 45,992 |
1111 | 400269 | 24-06-2022 | 50,000 | 1,000 | 46,992 |
1111 | 400269 | 24-06-2022 | 50,000 | -340 | 46,652 |
1111 | 400269 | 01-07-2022 | 50,000 | -4,577 | 42,075 |
1111 | 400269 | 01-07-2022 | 50,000 | 434 | 42,509 |
Please suggest me a way to create either measure or column.
@amitchandak @Ashish_Mathur @ChandeepChhabra @Fowmy @JihwanKim @Jihwan_Kim
Solved! Go to Solution.
@Anonymous , Assuming Opening Qty is in every row
a new column =
[Opening Qty] + sumx(filter(Table, [Plant] = earlier([Plant]) && [Material] = Earlier([Material]) && [Availbility Date] <= earlier([Availbility Date]) ),[Qty Utilized])
a new measure
Max(Table[Opening Qty]) + sumx(filter(allselected(Table), [Plant] = max([Plant]) && [Material] = max([Material]) && [Availbility Date] <= max([Availbility Date]) ),[Qty Utilized])
hello
first of all you should create Calendar table (_Calendar = Calendarauto())
create link between tables '_Calendar'[date] and 'Table'[Req/Availbility Date]
after it create measure
TotalYTD =
var _openQTY = min('Table'[ Opening Qty ])
var _YTD = TOTALYTD(SUM('Table'[ Qty Utilized ]),_Calendar[Date])
var _result = _openQTY+_YTD
return _result
then you can get result as bellow
If I answered at you question please mark topic as resolved 🙂
@Anonymous , Assuming Opening Qty is in every row
a new column =
[Opening Qty] + sumx(filter(Table, [Plant] = earlier([Plant]) && [Material] = Earlier([Material]) && [Availbility Date] <= earlier([Availbility Date]) ),[Qty Utilized])
a new measure
Max(Table[Opening Qty]) + sumx(filter(allselected(Table), [Plant] = max([Plant]) && [Material] = max([Material]) && [Availbility Date] <= max([Availbility Date]) ),[Qty Utilized])
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |