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.