DAX Moving Average Based on 4 Level Index

Question

I have trouble calculating MA for products' prices. Data is in the following format: Region has municipalities, products are sold there falling into certain categories by dates on a weekly basis, and not all weeks are filled due to seasonality.

I found a ranking formula and adjusted based on these 4 criteria. Here is the DAX expression for ranking (calculated column):

index2 = 
RANKX (
    FILTER (
        _2017,
        EARLIER ( _2017[RegionName] ) = _2017[RegionName] && 
        EARLIER ( _2017[MunicipalityName] ) = _2017[MunicipalityName] &&
        EARLIER (_2017[ProductCategoryName] )= _2017[ProductCategoryName] && 
        EARLIER ( _2017[ProductName] ) = _2017[ProductName]
    ),
    _2017[StartDateTime],
    ,
    ASC
)

After a change in product name, the index resets. All is good till here. But when I try to add any kind of running total according to this index, it seems to calculate prices' sum for all products, giving the same result at index reset and so on for every product.

Here are some measures I've tried:

cummulatives = 
VAR ind = MAX(_2017[index2])-3
VAR m1=
    CALCULATE(
        SUM(_2017[SellingPrice]),
            FILTER(
                ALL(_2017),
                _2017[index2]<=ind))
VAR m2=
    CALCULATE(
        COUNT(_2017[SellingPrice]),
            FILTER(
                ALL(_2017),
                _2017[index2]<=ind))
RETURN m2

Attached is an image of the table. Any help would be much appreciated. Thanks!

screen shot

Alexis Olson Alexis Olson · Accepted Answer · 2018-08-24T15:37:31

If you want the cumulative sum to reset with a new ProductName, then that has to be part of your filter context. You've removed that context using the ALL() function.

You can either put it back into the filter context or else not remove it in the first place. I would suggest the latter by using ALLEXCEPT(_2017, _2017[ProductName]) instead of ALL(_2017)

DAX Moving Average Based on 4 Level Index

1 Answers