Tutorial Request: Cumulative Sum by Date

[Anonymous]

Hi all,

I've done some searching and find myself struggling with a cummulative sum.  I was hoping someone here might be able to help me out.  Imagine you have data organized as follows:

Date	Project	Amount	Description
01/01/2020	#1	$25	January Invoice
02/01/2020	#1	$100	February Invoice
03/01/2020	#1	$150	March Invoice
03/01/2020	#2	$300	March Invoice

I want to build a table/graph with time on x-axis and cumulative  sum on the y-axis.  For example, it would show $25 on 1/1/2020 and $125 for Project #1 in January and February, respectfully.  Whereas it would show $300 for Project #2 in March.

Any help would be much appreciated!

[V-pazhen-msft]

@Anonymous 

Typical running total measure is something like:

measure = Calculate(Sum([Amount]), Filter(All(table), Table[Date]<=Max(Table[Date]))
OR
measure = Calculate (Sum([Amount]),Filter(All[Table), Sumx(filter(table,earlier([Date])<=[Date] && earlier([Project])=[Project]),1))

In case if you need running total by Project: 

measure = Calculate(Sum([Amount]), Filter(All(table), Table[Date]<=Max(Table[Date]) && [Project]=MAX([Project])
OR 
measure = Calculate (Sum([Amount]),Filter(All[Table), Sumx(filter(table,earlier([Date])<=[Date] && earlier([Project])=[Project]),1))

Correct me if i missed any brackets.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  

[Anonymous · Accepted solution]

Thank you all for the help.  Unfortunately, the best I could get with these formulas was to return a few random sums, but not a cumulative.

After much struggling and guessing, I was able to figure it out.  For others curious, this is the working code:

column = calculate(sumx(filter(ALLSELECTED('Transactions: Invoice Details'),'Transactions: Invoice Details'[ProjectID]=max('Transactions: Invoice Details'[ProjectID]) && 'Transactions: Invoice Details'[Invoice Date]<=max('Transactions: Invoice Details'[Invoice Date])),[Billed/Credit Amount]))

I can't explain why the other solution without using ALLSELECTED() would not work.  It looks like it should.  I don't know DAX all that well, but I need to look more into this calculate function.  It seemed to be a key component in fixing my issue.

[Greg_Deckler]

@Anonymous - Perhaps:

Cumulative Amount Column =
  SUMX(FILTER('Table',[Project] = EARLIER([Project]) && [Date] <= EARLIER([Date])),[Amount])