cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
oscarII Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Super User
Super User

Re: cumulative for certain period

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

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

16 REPLIES 16
Super User
Super User

Re: cumulative for certain period

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

Re: cumulative for certain period

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

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

oscarII Frequent Visitor
Frequent Visitor

Re: cumulative for certain period

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

oscarII Frequent Visitor
Frequent Visitor

Re: cumulative for certain period

@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 New Contributor
New Contributor

Re: cumulative for certain period

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 Frequent Visitor
Frequent Visitor

Re: cumulative for certain period

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

aruntd77 Regular Visitor
Regular Visitor

Re: cumulative for certain period

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

Highlighted
Willborn Member
Member

Re: cumulative for certain period

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

ChristianH Frequent Visitor
Frequent Visitor

Re: cumulative for certain period

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 387 members 3,656 guests
Please welcome our newest community members: