Cumulative Total By Date

[Kurt4597]

Hi all,

Stuck on this issue - any help would be much appreciated

DateDim

Sales

I am trying to create a measure that is a cumulative total of the sales and is able to be filtered by the date table. I have the following measure which produces the cumalative sales

Cumalatve Sales = 
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER (
        ALL ( DateDim[Date] ),
        DateDim[Date] <= MAX ( DateDim[Date] )
    )
)

However it not adjusting correctly for the date. As you can see from the graph, the first value is 1,246 which is a cumulative total of the previous three days. Instead it should start at 151 as the date filter is set to 5th March

 

Any help would be much appreciated. 
Sample File - 

[Anonymous · Accepted solution]

Hi  @Kurt4597 , 

You can modify Measure to the following form:

Measure = 
CALCULATE( 
    SUM('Sales'[Sales]), 
    FILTER(ALLSELECTED(DateDim), 
    'DateDim'[Date]<=MAX('DateDim'[Date])) 
    ) 

Result: 

Best Regards, 

Liu Yang 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly