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

Cumulative Total

Hello.  I am trying to create a cumulative total for each month but I am getting data per month which I don't want to have. I used this measure

 

Cumulative = CALCULATE(
SUM(Finance[Column CC]),
FILTER(ALL(Finance[Date]), Finance[Date] <= MAX (Finance[Date])))

 

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
cabelo Resolver I
Resolver I

Re: Cumulative Total

Filtering all Finance Table (not only a column), I get the Cumulative for the month, but if you have duplicate dates, the cumulative will be the same, in both.

 

Cumulative = CALCULATE(
SUM(Finance[Column CC])
,FILTER(ALL(Finance), Finance[Date] <= MAX (Finance[Date])))

 

 

Cumulative.png

View solution in original post

16 REPLIES 16
tringuyenminh92 Memorable Member
Memorable Member

Re: Cumulative Total

I think your formula is correct, so could you please provide more information:

  • Control you are using (charts/table/matrix)
  • Sample data
cabelo Resolver I
Resolver I

Re: Cumulative Total

I use the same formula, and works.

 

Can you take a print? or sample data? The Financial[Date] have 1 date per month?

More details can help.

Maram
Frequent Visitor

Re: Cumulative Total

The data is coming from each centre and is collected once every month and is split by Actual and Budget.

Centre

Date

Income

Staff

Agency

Other costs 

Act/Bud

1

31/03/2015

-2000

200

200

122

Act

2

31/03/2015

-4000

100

100

100

Act

1

31/03/2015

-2000

200

200

122

Bud

2

31/03/2015

-4000

100

100

100

Bud

1

30/04/2015

-2000

200

200

122

Act

2

30/04/2015

-4000

100

100

100

Act

1

30/04/2015

-2000

200

200

122

Bud

2

30/04/2015

-4000

100

100

100

Bud

 

The data is mocked up but you get the general idea.

 

To get the Column CC I created a cal column which is Column CC = (-Finance[Income] - Finance[Staff] - Finance[Agency]- Finance[Other costs])

 

I am using bar graph.

 

I hope it is clear, if not let me know.

 

tringuyenminh92 Memorable Member
Memorable Member

Re: Cumulative Total

 

 

@Maram

You need to add one more Dates table and link your date column to this date:

Refer: https://community.powerbi.com/t5/Desktop/DAX-How-to-perform-a-cummulative-summation/td-p/7378

Maram
Frequent Visitor

Re: Cumulative Total

@tringuyenminh92 I dont understand why I need to create another date table when I do have one already.

cabelo Resolver I
Resolver I

Re: Cumulative Total

Filtering all Finance Table (not only a column), I get the Cumulative for the month, but if you have duplicate dates, the cumulative will be the same, in both.

 

Cumulative = CALCULATE(
SUM(Finance[Column CC])
,FILTER(ALL(Finance), Finance[Date] <= MAX (Finance[Date])))

 

 

Cumulative.png

View solution in original post

Maram
Frequent Visitor

Re: Cumulative Total

@cabelo  It is what I get as well. I would like to have cumulative actual and budget in one graph. Is there any way of doing this?

tringuyenminh92 Memorable Member
Memorable Member

Re: Cumulative Total

Hi @Maram When using All(Original Table) you will lost the filter of another column/categories  in this table. And you have to workaround by using AllExcept to fix that problem. But with seperate Dates table, it will not affect to any filter in Fact table. 

But your question is so great, if I have chance to meet my idol Marco Russo, I will ask him to explain or confirm about my understand of his design in this time pattern: http://www.daxpatterns.com/time-patterns/

 

 

@cabelo I don't think the duplicated(multiple transaction in same date) is the problem with his situation cause he is using filter ALL.

 

 

tringuyenminh92 Memorable Member
Memorable Member

Re: Cumulative Total

@Maram It could be, just let us know how your data format of fact and budget are (same table or separate tables). I will provide suitable solution same as https://community.powerbi.com/t5/Desktop/Allocate-monthly-target-to-day/m-p/97285#M40987 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors