cumulative for certain period

[oscarII]

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 · Accepted solution]

@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]

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]

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?

[dwolfe1013]

Hi!  I am trying to use your solution and it does not seem to work for me.

I have tried both of the following expressions, and both return the same results.  The values returned are cumulative back to the start of the query, rather than cumulative for only the date range shown in the visual.

Any ideas why the addition of the allselected function does not appear to be affecting the returned totals?

Thank you!

cumulative total=
CALCULATE (
    SUM ( 'table'[column]),
    FILTER (
        ALLSELECTED ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

-or-

cumulative total=
CALCULATE (
    SUM ( 'table'[column]),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

[oscarII]

@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]

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.

[aruntd77]

Hi,

I tried your approach to eliminate the future dates, however it is not filtering.

Cumulative Actual Units = CALCULATE(SUM('DWH W_ORDER_HEADER_D'[Sold Count]), FILTER(ALLSELECTED('DWH W_DAY_D'), 'DWH W_DAY_D'[CALENDAR_DATE] <= MAX('DWH W_DAY_D'[CALENDAR_DATE]) && 'DWH W_DAY_D'[CALENDAR_DATE] <= TODAY()))

Please help

Thanks

Arun

[Willborn]

Hi Arun

I struggle with the same issue. The expression <= TODAY() is somehow not working.

Did you solved this already? If yes - how?

Regards Patrick

[aruntd77]

Hi Willborn,

I know I am replying this very late. Yes we had fixed it by adding one additional column in Date Dimension Table called "TillDate". We have this table in SQL DB, and get updated on daily basis. The Till Date column will propulate all the dates till today, any records beyond today's date will be set to blank. We also have Calender Date column which is populated for all dates in Date dimension table.

So, for actuals till today, we are using below formula. Please note on <= MAX(datedim[TillDate]) clause.

Sold Cumulative = CALCULATE(Calculate(Sum(FactsActuals[OrderNetsellingPrice]),FactsActuals[Custom_Order_Status]="SOLD",FactsActuals[UnitType]<>"RETAIL")-Calculate(Sum(FactsActuals[OrderNetsellingPrice]),FactsActuals[Custom_Order_Status]="Cancelled",FactsActuals[StatusBeforeCancellation]="Sold",FactsActuals[UnitType]<>"RETAIL"),filter(ALLSELECTED(DateDim),DateDim[Calendar_date]<=max(DateDim[Tilldate])))

The above will be restricted because remaining dates for the year after today is set to BLANK OR NULL, so the chart will not consider blank values.

We use Target Sales formula as below. In this case, it uses Filter Context, where in I would have selected year as 2017, so it uses only the dates that are in the filter context and since I am considering Calendar_date column which has dates for full year. So, you will see cumulative numbers for the full year in this case.

Target Sales Cumulative = CALCULATE(sum(FactTarget[TargetValue]),filter(ALLSELECTED(DateDim),DateDim[Calendar_date]<=max(DateDim[Calendar_date])),FactTarget[ACTIVE_FLG]="Y")

[Anonymous]

Hi All

I've got the issue where I want my data visuals to be dynamic to the dates but for some reason it's still cumulating data from the previous dates.

For example, I have a data set that spreads from 01/01/2016 to the current date. I want to be able to use a drop down menu to select which year to display a chart Jan - Dec.

When I select 2016, it works fine...because I don't have any data from 2015. But when I select 2017, it takes data from 2016 and keeps cumulating into Jan. Anyone know how I can start cumulating only for the date range I've selected?

Note, this is my formula:

Cumulative Spend = CALCULATE (
    SUM ( [Amount (Excl VAT)] ),
    FILTER (
        ALLSELECTED('Vendor Ledger'[Document Date].[Date]),
        'Vendor Ledger'[Document Date].[Date] <= MAX ( 'Vendor Ledger'[Document Date].[Date] )
     && 'Vendor Ledger'[Document Date].[Date] <= TODAY()  )
)

Thank you

[Anonymous]

Any one know why it does this?

I've selected previous 12 calendar months.

[Anonymous]

Expanding on ChristianH comments, I suggest using Blank() instead of 0

Cummulative By Year = IF(max(Dates[Date])>Dates[Current Date], BLANK(), CALCULATE(SUM('Table'[Field]), DATESYTD(Dates[Date])))

[ChristianH]

Hi,

I ran in to the same problem and fixed it with a simple IF-statement, which sets the measure to zero if the MAX of date is higher than TODAY():

Cumulative no of leads = IF(max(Dates[Dates])>today(),0,CALCULATE(COUNTA('lead'[fullname]),Filter(ALLSELECTED(Dates),Dates[Date]<=max(Dates[Date])

Regards,
Christian

[oscarII]

Many thanks @greggyb, we needed to make some adjustments to suit our dataset but have managed to resolve this now!

[oscarII]

@Greg_Deckler - Thanks very much, that's working perfectly now 

[Greg_Deckler]

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