Calculating cumulative weekly average demand

Hi All,

I’ve discussed this briefly in one of @dataray’s topics , but I thought I’d post this out to the wider community.

I have a stock report that runs from the current week to the end of the current financial year which shows expected demand, Last 3 months demand and Average Weekly demand for the previous 3 months. It should also calculate the cumulative Average Demand for the previous 3 months, but I just can’t seem to work out the DAX to achieve this.

Here is the DAX formula that I’m using:

``````cumulative wk =
VAR getDate = MAX( 'Fiscal Dates'[Calendar Year and ISOWeek] ) -- Store current week
VAR warehouse = MAX( Warehouses[Warehouse Code] )
VAR productCode = MAX( Products[Product Code] )
VAR AvgRunningTot = CALCULATE( SUMX( 'Stock Demand', [Avg Weekly]), FILTER( ALL( 'Fiscal Dates' ), 'Fiscal Dates'[Calendar Year and ISOWeek] <= getDate ), FILTER( ALLSELECTED( Warehouses ), Warehouses[Warehouse Code] = warehouse ), FILTER( ALLSELECTED( Products ), Products[Product Code] = productCode ) )
RETURN
AvgRunningTot
``````

As you can see in the image, the cumulative average is incorrect. I have manually added the column with the desired result.

Any help would be much appreciated.

Thanks,

Pete.

Hi @pete962, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

• Use the forum search to discover if your query has been asked before by another member.

• Use the proper category that best describes your topic

• Provide as much context to a question as possible.

• Include the masked demo pbix file and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.