Forum Discussion
Accumulate Column
Hello,
I have a list of contracts and am trying to figure out a way to accumulate the sum of active contracts over the year.
My Datatable looks something like this:
| Counter | StartDate | Enddate | ||
| 1 | 01.01.2024 | 31.12.2028 | ||
| 1 | 01.01.2024 | 31.12.2026 | ||
| 1 | 01.01.2024 | 01.08.2026 | ||
| 1 | 01.01.2023 | 01.02.2024 | ||
| 1 | 01.01.2002 | 01.03.2024 | ||
| 1 | 01.01.2004 | 01.06.2024 | ||
| 1 | 01.03.2024 | 01.08.2026 | ||
| 1 | 01.03.2024 | 01.08.2026 | ||
| 1 | 01.06.2024 | 01.08.2026 | ||
| 1 | 01.02.2024 | 01.08.2026 | ||
| 1 | 02.02.2024 | 01.08.2026 | ||
| 1 | 03.02.2024 | 01.08.2026 | ||
| 1 | 04.03.2024 | 01.08.2026 | ||
| 1 | 05.05.2024 | 01.08.2026 | ||
| 1 | 06.06.2024 | 01.08.2026 | ||
| 1 | 07.07.2024 | 01.08.2026 | ||
| 1 | 01.01.2013 | 01.02.2024 | ||
| 1 | 01.01.2012 | 01.08.2024 | ||
| 1 | 01.01.2015 | 01.09.2024 | ||
| 1 | 01.01.2016 | 01.03.2024 |
Now its no problem to calculate the to number of active contracts for any given month, but I cant figure out a way to accumulate them over the year. My goal would be a table that looks like this for the current example:
| Date | Counter | Accumulated | ||
| 01.01.2024 | 10 | 10 | ||
| 01.02.2024 | 11 | 21 | ||
| 01.03.2024 | 13 | 34 | ||
| 01.04.2024 | 12 | 46 | ||
| 01.05.2024 | 12 | 58 | ||
| 01.06.2024 | 14 | 72 | ||
| 01.07.2024 | 14 | 86 | ||
| 01.08.2024 | 15 | 101 | ||
| 01.09.2024 | 14 | 115 | ||
| 01.10.2024 | 13 | 128 | ||
| 01.11.2024 | 13 | 141 | ||
| 01.12.2024 | 13 | 154 |
I could not find an example for this specific Problem on the Forums and all solutions I found are not quite working for this situation.
Any help or ideas would be greatly appreciated.
Hi,
For calculating running totals I recommend cheking this article: Computing running totals in DAX - SQLBI
For your case here is an example:Column =VAR MaxDate = [Date] -- Saves the last visible dateRETURNCALCULATE (SUM('Table (39)'[Counter]), -- Computes sales amount'Table (39)'[Date] <= MaxDate, -- Where date is before the last visible dateALL ( 'Table (39)') -- Removes any other filters from Date)
This is for a column like you requested, but I recommend using a measure. Also I recommend adding calendar table to your model.
End result:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
1 Reply
Hi,
For calculating running totals I recommend cheking this article: Computing running totals in DAX - SQLBI
For your case here is an example:Column =VAR MaxDate = [Date] -- Saves the last visible dateRETURNCALCULATE (SUM('Table (39)'[Counter]), -- Computes sales amount'Table (39)'[Date] <= MaxDate, -- Where date is before the last visible dateALL ( 'Table (39)') -- Removes any other filters from Date)
This is for a column like you requested, but I recommend using a measure. Also I recommend adding calendar table to your model.
End result:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/