[Power Query] Summing values across all dimensions except one

Hello Gurus!

I am seeking your help again with a task at hand which I found a bit complicated.

In the warehouse, we take a snapshot of the "Closing Stock" by end of each month. It makes sense to aggregate the closing stock by several dimensions such as "Production Line", "Production Plant", etc..., but it doesn't make any sense to aggregate the closing stocks for several time periods because it would be meaningless.

1.JPG

For example, if I am looking at the stock closing of a quarter, let's say Q1-2023, it shouldn't add Jan + Feb + Mar, but instead it should only look at Mar-2023 as the closing stock for this quarter, and add everything under March-2023 only.

2.JPG

Similarly, if I am looking at all data without adding any time dimension, it should add the closing stocks under the last month in the dataset, but *not* add the closing stock for all months because it doesn't mean anything.

3.JPG

I have added a detailed sample file with what I'm trying to achieve and the expected results. Could you please help me write the DAX formula that would achieve that objective?

The logic I have in mind would be basically to tell Excel to "SUM everything under the last month you find under any selection".

Thank You!

Sample Data for Closing Stock Aggregation.xlsx

Hi Ali,

Unfortunately I'm stuck with PQ and DAX because this problem is a part of a huge data model made using PQ.

Thank You.

Hi JeteMC,

Thank you for your reply. I don't think it would be usable in my case because the data is dynamic and I need the model to be able to calculate properly on its own.

I have managed to resolve it finally after a few trials using a simple DAX equation. I got the idea when I started breaking down the solution on a piece of paper.

Solution.JPG

Resolved.JPG

Solution Workbook: Sample Data for Closing Stock Aggregation (DAX Solution).xlsx

Thank You.