cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
oscarII
New Member

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! 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

16 REPLIES 16
Greg_Deckler
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

 

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() ) )

 

Capture.JPG

 

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?

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

 

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

 

 

Cumulative chart.JPG

 

 

 

 

 

 

 

 

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
Resident Rockstar

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.

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

 

Running Total.png

 

Thanks

Arun

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

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")

 

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?

 

2016.PNG2017.PNG

 

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

Any one know why it does this?

 

I've selected previous 12 calendar months.

 

Capture.PNG

Anonymous
Not applicable

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

 

 

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

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

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

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!