Forum Discussion
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])))
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.
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.