Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
@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.
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.
@Anonymous - Perhaps:
Cumulative Amount Column =
SUMX(FILTER('Table',[Project] = EARLIER([Project]) && [Date] <= EARLIER([Date])),[Amount])