Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

@Anonymous  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?

@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 

Hi @tringuyenminh92  Thanks for the reply 🙂 I am new to DAX so how would you use it?  I did separate the table into actual and budget before, but it doesnt really work out in the long term as I want to use the same data to create new graphs.  PS its she 😛

 

@Anonymous Everything is in one table. It would be great if you could find a solution! If there is anything you need, do let me know. I appreciate it 🙂

For DAX, i'm new too ^_^ just write down what i want to do and then searching related functions article & topics.
@Maram Back to your question about actual & budget, could you please show the current sample data of actual and budget in picture/file?

And your expectation in file/picture, so I could figure out the solution. 

@tringuyenminh92 Oh good that mean I am not only one who is new to DAX!


For some reason I cannot put the actual data as I get a blank with a red cross on the photo. Hence the best I can do is create mock numberical data  but the structure of database is same.

 

Period

CC No

Service

Region

Serv Type

Income

Staff

Agency

Other costs

Year

Month

Act/Bud

Date

2016-01

1

A

East

Care Home

20,000

10,000

2,000

1,000

2016

Apr

Actual

30/04/2015

2016-01

2

B

East

Care Home

10,000

7,000

1,000

2,000

2016

Apr

Actual

30/04/2015

2016-01

3

C

East

Care Home

15,000

13,000

3,000

4,000

2016

Apr

Actual

30/04/2015

2016-01

4

D

East

Care Home

13,000

11,000

3,000

1,000

2016

Apr

Actual

30/04/2015

2016-01

5

E

East

Supported Living

10,000

6,000

1,000

2,000

2016

Apr

Actual

30/04/2015

2016-01

1

A

East

Care Home

35,000

20,000

1,000

1,000

2016

Apr

Budget

30/04/2015

2016-01

2

B

East

Care Home

20,000

14,000

1,000

2,000

2016

Apr

Budget

30/04/2015

2016-01

3

C

East

Care Home

25,000

18,000

3,000

4,000

2016

Apr

Budget

30/04/2015

2016-01

4

D

East

Care Home

13,000

11,000

2,000

2,000

2016

Apr

Budget

30/04/2015

2016-01

5

E

East

Supported Living

19,000

16,000

1,000

1,500

2016

Apr

Budget

30/04/2015

12345.png

 

111.png

^^This what I expected (the only issue is it is not cumulative)

Hope this explain everything, if not let me know.

Same formula for Calculated Measure of cummulative, what stopping you to have the actual & budget cummulative?

Please check my files and sample data:

Screenshot 2016-12-05 23.41.12.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

CC = -Transactions[InCome] -Transactions[Staff] -Transactions[Agency]-Transactions[Other costs]

 

 

I will go to sleep after this comment cause it's late here. see u tomorrow @Maram

 

 

@tringuyenminh92

 

Aha thank you. It turns out I need to get Date from different table, my measure now look like this :

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

 

I only have the actual data from April to Sept, and budget data from April to March.  The graph is showing actual data from April to March, even though I don't have the actual data for actual from Sept to March. Is there a way to show zero or no actual data from Oct to March?


   Nearly there.png

Hi @Maram,

 

I'm sorry but i could not understand your description, could you please explain again? And if you take a look my picture, there is no actual data in July so it's empty. That's default with no configuration.

Hi @tringuyenminh92

 

Your data samples were helpful.  The measure is working.

 

Thank you for your help 🙂

 

 

Anonymous
Not applicable

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.

tringuyenminh92
Memorable Member
Memorable Member

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

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

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.

 

 

 

@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

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

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/

 

 

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

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.