Calculating cumulative weekly average demand

DAX DAX Calculations
1

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.

image
image1214×252 18.2 KB

Any help would be much appreciated.

Thanks,

Pete.

2

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.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.