Forum Discussion

Kurt4597's avatar
Kurt4597
Helper I
3 years ago
Solved

Cumulative Total By Date

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 - 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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 
     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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