Accumulate Column

[TKBI]

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. 

[ValtteriN · Accepted solution]

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 date

RETURN

    CALCULATE (

        SUM('Table (39)'[Counter]),            -- Computes sales amount

        'Table (39)'[Date] <= MaxDate,   -- Where date is before the last visible date

        ALL ( '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/