Spreadsheet: 858110d1706870831-power-query-summing-values-across-all-dimensions-except-one-sample-data-for-closing-stock-aggregation
Sheet: Closing Stock Sample

Product Number | Production Line | Production Plant | Month | Quarter | Production ([NAME]) | Demand ([NAME]) | Closing Stock for Period ([NAME]) |  | Problem Description
Product 1 | [NAME] | Plant X | 2023-01-01 00:00:00 |  | 47014 | 45211 | 12164 |  | I need to write a DAX calculated field to calculate the "Aggregated Closing Stock" for the data using the column highlighted in yellow.
Product 1 | [NAME] | Plant X | 2023-02-01 00:00:00 |  | 56061 | 4976 | 19372 |  | The closing stock is a snapshot in time, so it shouldn't sum values under different periods, but instead it should sum values for the "last" period found under any selection.
Product 1 | [NAME] | Plant X | 2023-03-01 00:00:00 |  | 38821 | 16166 | 6467
Product 1 | [NAME] | Plant X | 2023-04-01 00:00:00 |  | 68235 | 16675 | 24012 |  | In the first example below, the Closing stock for Q1 should be the sum of Mar-2023 figures only, since it's the last period in the quarter, but NOT Jan + Feb + Mar which is meaningless.
Product 1 | [NAME] | Plant X | 2023-05-01 00:00:00 |  | 94131 | 71212 | 38276 |  | Similarly, the closing stock for Q2, is actually the sum of everything under June-2023 figures only.
Product 1 | [NAME] | Plant X | 2023-06-01 00:00:00 |  | 97980 | 93288 | 27797
Product 1 | [NAME] | Plant X | 2023-07-01 00:00:00 |  | 76519 | 73794 | 27086 |  | In this example, I would also expect that the "Grand Total" for columns should be exactly equal to Q4 (which is Dec-23 totals) since the last period under "Grand Total" will be also Dec-23.
Product 1 | [NAME] | Plant X | 2023-08-01 00:00:00 |  | 1982 | 53869 | 49860
Product 1 | [NAME] | Plant X | 2023-09-01 00:00:00 |  | 62669 | 83065 | 28136 |  |  | Data Model |  |  |  |  |  |  | Expected Results (Manual Calculation)
Product 1 | [NAME] | Plant X | 2023-10-01 00:00:00 |  | 18686 | 20408 | 33714
Product 1 | [NAME] | Plant X | 2023-11-01 00:00:00 |  | 51823 | 75329 | 20270 |  |  | Quarters | 1 | 2 | 3 | 4 | Grand Total |  | Quarters | 1 | 2 | 3 | 4 | Grand Total
Product 1 | [NAME] | Plant X | 2023-12-01 00:00:00 |  | 42838 | 87946 | 5949 |  |  | Plant X |  |  |  |  |  |  | Plant X
Product 2 | Funky Line | Plant X | 2023-01-01 00:00:00 |  | 95759 | 62587 | 33993 |  |  | Funky Line |  |  |  |  |  |  | Funky Line
Product 2 | Funky Line | Plant X | 2023-02-01 00:00:00 |  | 39375 | 32007 | 26899 |  |  | [NAME] |  |  |  |  |  |  | [NAME]
Product 2 | Funky Line | Plant X | 2023-03-01 00:00:00 |  | 4435 | 4103 | 27106 |  |  | Plant Y |  |  |  |  |  |  | Plant Y
Product 2 | Funky Line | Plant X | 2023-04-01 00:00:00 |  | 82094 | 25344 | 19526 |  |  | [NAME] |  |  |  |  |  |  | [NAME]
Product 2 | Funky Line | Plant X | 2023-05-01 00:00:00 |  | 91683 | 33096 | 47350 |  |  | Grand Total |  |  |  |  |  |  | Grand Total
Product 2 | Funky Line | Plant X | 2023-06-01 00:00:00 |  | 87220 | 26372 | 9623
Product 2 | Funky Line | Plant X | 2023-07-01 00:00:00 |  | 50156 | 92693 | 42515 |  | In the second aggregation example below, since I haven't even included months/quarters in the pivot table dimensions, I expect it to SUM everything under the last available month
Product 2 | Funky Line | Plant X | 2023-08-01 00:00:00 |  | 32417 | 39962 | 23670 |  | in all the data, which is "December-2023 in this case.
Product 2 | Funky Line | Plant X | 2023-09-01 00:00:00 |  | 56221 | 89221 | 33363
Product 2 | Funky Line | Plant X | 2023-10-01 00:00:00 |  | 54018 | 88632 | 41347 |  |  | Data Model |  |  |  |  |  |  | Expected Results (Manual Calculation)
Product 2 | Funky Line | Plant X | 2023-11-01 00:00:00 |  | 33918 | 65280 | 37043 |  |  | Row Labels |  |  |  |  |  |  | Row Labels | Closing | Grand Total
Product 2 | Funky Line | Plant X | 2023-12-01 00:00:00 |  | 24202 | 22922 | 44240 |  |  | Plant X |  |  |  |  |  |  | Plant X
Product 3 | [NAME] | Plant Y | 2023-01-01 00:00:00 |  | 88846 | 53292 | 34413 |  |  | Funky Line |  |  |  |  |  |  | Funky Line
Product 3 | [NAME] | Plant Y | 2023-02-01 00:00:00 |  | 96423 | 30899 | 7613 |  |  | [NAME] |  |  |  |  |  |  | [NAME]
Product 3 | [NAME] | Plant Y | 2023-03-01 00:00:00 |  | 80570 | 22773 | 27122 |  |  | Plant Y |  |  |  |  |  |  | Plant Y
Product 3 | [NAME] | Plant Y | 2023-04-01 00:00:00 |  | 31067 | 9836 | 25104 |  |  | [NAME] |  |  |  |  |  |  | [NAME]
Product 3 | [NAME] | Plant Y | 2023-05-01 00:00:00 |  | 34753 | 33918 | 40132 |  |  | Grand Total |  |  |  |  |  |  | Grand Total
Product 3 | [NAME] | Plant Y | 2023-06-01 00:00:00 |  | 75231 | 28957 | 40180
Product 3 | [NAME] | Plant Y | 2023-07-01 00:00:00 |  | 60247 | 73369 | 15736 |  | Could you please help me with the DAX formulat that would achieve that? It should basically tell Excel to "SUM everything under the last month you find under any selection".
Product 3 | [NAME] | Plant Y | 2023-08-01 00:00:00 |  | 21938 | 29958 | 45036
Product 3 | [NAME] | Plant Y | 2023-09-01 00:00:00 |  | 43587 | 90973 | 43622
Product 3 | [NAME] | Plant Y | 2023-10-01 00:00:00 |  | 59142 | 29857 | 5587
Product 3 | [NAME] | Plant Y | 2023-11-01 00:00:00 |  | 4851 | 44108 | 14341
Product 3 | [NAME] | Plant Y | 2023-12-01 00:00:00 |  | 65304 | 30355 | 14589
Product 4 | [NAME] | Plant X | 2023-01-01 00:00:00 |  | 34692 | 49050 | 19424
Product 4 | [NAME] | Plant X | 2023-02-01 00:00:00 |  | 60844 | 86232 | 14160
Product 4 | [NAME] | Plant X | 2023-03-01 00:00:00 |  | 24382 | 51136 | 13918
Product 4 | [NAME] | Plant X | 2023-04-01 00:00:00 |  | 91722 | 40242 | 40399
Product 4 | [NAME] | Plant X | 2023-05-01 00:00:00 |  | 91723 | 85749 | 7358
Product 4 | [NAME] | Plant X | 2023-06-01 00:00:00 |  | 45831 | 64888 | 22524
Product 4 | [NAME] | Plant X | 2023-07-01 00:00:00 |  | 2987 | 55026 | 11765
Product 4 | [NAME] | Plant X | 2023-08-01 00:00:00 |  | 47415 | 44534 | 30425
Product 4 | [NAME] | Plant X | 2023-09-01 00:00:00 |  | 80782 | 45642 | 10317
Product 4 | [NAME] | Plant X | 2023-10-01 00:00:00 |  | 48050 | 79677 | 28454
Product 4 | [NAME] | Plant X | 2023-11-01 00:00:00 |  | 92968 | 18977 | 39220
Product 4 | [NAME] | Plant X | 2023-12-01 00:00:00 |  | 89697 | 45216 | 23745
Product 5 | [NAME] | Plant Y | 2023-01-01 00:00:00 |  | 42316 | 75223 | 21033
Product 5 | [NAME] | Plant Y | 2023-02-01 00:00:00 |  | 87926 | 47099 | 2872
Product 5 | [NAME] | Plant Y | 2023-03-01 00:00:00 |  | 65813 | 88114 | 6219
Product 5 | [NAME] | Plant Y | 2023-04-01 00:00:00 |  | 68302 | 95854 | 31410
Product 5 | [NAME] | Plant Y | 2023-05-01 00:00:00 |  | 87229 | 2098 | 11376
Product 5 | [NAME] | Plant Y | 2023-06-01 00:00:00 |  | 93659 | 67853 | 17114
Product 5 | [NAME] | Plant Y | 2023-07-01 00:00:00 |  | 6950 | 84107 | 40197
Product 5 | [NAME] | Plant Y | 2023-08-01 00:00:00 |  | 93190 | 35426 | 17041
Product 5 | [NAME] | Plant Y | 2023-09-01 00:00:00 |  | 63335 | 69459 | 45702
Product 5 | [NAME] | Plant Y | 2023-10-01 00:00:00 |  | 65344 | 66104 | 18608
Product 5 | [NAME] | Plant Y | 2023-11-01 00:00:00 |  | 51048 | 76478 | 18808
Product 5 | [NAME] | Plant Y | 2023-12-01 00:00:00 |  | 90274 | 52128 | 26424