We are creating a measure to cumulate Sales figures, so we can see cumulated sales for the last month/year etc, or any filtered period. The table we are summing contains a line for each invoice line with the exact transaction date. We are using the following formula: CALCULATE(SUM('Invoice lines'[Sales]),FILTER(ALL('BI-Dates'[Date]),'BI-Dates'[Date]<=MAX('BI-Dates'[Date])),'BI-Dates'[Date]) However, when you filter (using a slicer) for a specific timeframe, the total at the start of that period shows the accumulated total to that point, whereas it should start at zero and then accumulate throughout the period. If anyone can kindly help out, that would be great, thanks!

oscarII - OK played with this a bit, tested some things out and fixed a column format issue on my end. Here is what I came up with that I believe will get you what you want: Cumulative 2 = CALCULATE ( SUM ( 'Invoice lines'[Sales] ), FILTER ( ALLSELECTED(BI-Dates), 'BI-Dates'[Date] <= MAX ( 'BI-Dates'[Date] ) ) )The ALLSELECTED restricts the table returned to only the time range selected. I was able to put BI-Dates[Date] and Cumulative 2 in a table and watch the running total add up each day. I also used a [Month] column from BI-Dates and was able to get different (correct) cumulative totals for January versus February.

oscarII

New Member

10 years ago

Solved

cumulative for certain period

We are creating a measure to cumulate Sales figures, so we can see cumulated sales for the last month/year etc, or any filtered period. The table we are summing contains a line for each invoice line with the exact transaction date.

We are using the following formula:

CALCULATE(SUM('Invoice lines'[Sales]),FILTER(ALL('BI-Dates'[Date]),'BI-Dates'[Date]<=MAX('BI-Dates'[Date])),'BI-Dates'[Date])

However, when you filter (using a slicer) for a specific timeframe, the total at the start of that period shows the accumulated total to that point, whereas it should start at zero and then accumulate throughout the period.

If anyone can kindly help out, that would be great, thanks!

Greg_Deckler
10 years ago
oscarII - OK played with this a bit, tested some things out and fixed a column format issue on my end. Here is what I came up with that I believe will get you what you want:

Cumulative 2 = CALCULATE ( SUM ( 'Invoice lines'[Sales] ), FILTER ( ALLSELECTED(BI-Dates), 'BI-Dates'[Date] <= MAX ( 'BI-Dates'[Date] ) ) )
The ALLSELECTED restricts the table returned to only the time range selected. I was able to put BI-Dates[Date] and Cumulative 2 in a table and watch the running total add up each day. I also used a [Month] column from BI-Dates and was able to get different (correct) cumulative totals for January versus February.

16 Replies

Greg_Deckler
Community Champion
10 years ago
oscarII - OK played with this a bit, tested some things out and fixed a column format issue on my end. Here is what I came up with that I believe will get you what you want:

Cumulative 2 = CALCULATE ( SUM ( 'Invoice lines'[Sales] ), FILTER ( ALLSELECTED(BI-Dates), 'BI-Dates'[Date] <= MAX ( 'BI-Dates'[Date] ) ) )
The ALLSELECTED restricts the table returned to only the time range selected. I was able to put BI-Dates[Date] and Cumulative 2 in a table and watch the running total add up each day. I also used a [Month] column from BI-Dates and was able to get different (correct) cumulative totals for January versus February.
- Maxime
  Advocate I
  9 years ago
  
  You should rather compare the Maximum's date to today's date like this:
  
  Cumulative 2 = CALCULATE ( SUM ( 'Invoice lines'[Sales] ), FILTER ( ALLSELECTED(BI-Dates), 'BI-Dates'[Date] <= MAX ( 'BI-Dates'[Date] ) && MAX ( 'BI-Dates'[Date] ) <= TODAY() ) )
  - brianhackett5
    Helper I
    9 years ago
    
    Hi there,
    
    I need to have the cumulative kilometres from another table showing on this table. The cumulative kilometres would need correspond with the dates on the above table.
    Is this possible?
- oscarII
  New Member
  10 years ago
  Greg_Deckler... Just one more point on this - the cumulative amount runs past the end of the available data and you end up with a graph looking like this:
  
  Is there any way that the formula can be amended to only show where there is data against that particular date?
  
  Thanks for the help.
  - greggyb
    Resident Rockstar
    10 years ago
    Cumulative 2 = CALCULATE ( SUM ( 'Invoice lines'[Sales] ), FILTER ( ALLSELECTED(BI-Dates), 'BI-Dates'[Date] <= MAX ( 'BI-Dates'[Date] ) && 'BI-Dates'[Date] <= TODAY() ) )
    Just remove all dates greater than the system date from the date dimension. && is the logical and operator.
- oscarII
  New Member
  10 years ago
  Greg_Deckler - Thanks very much, that's working perfectly now :smileyhappy:
Greg_Deckler
Community Champion
10 years ago
OK, I set this up with a simple date table, BI-Dates consisting of Date and Month and an Invoice lines table consisting of Date and Sales. I then pasted in your meaure as stated below. Without a relationship between the tables, the measure always returned the same thing, the cumulative total overall. With a relationship on Date, the measure returned nothing always. So, going to play with this some more and see if I can get the running total working but you might want to check out:

http://www.daxpatterns.com/cumulative-total