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