Running Totals Over Extended Period of Time

[aulla]

Hi

I have a table just like this:

Created	Product	Delincuency	Value
1/1/2024	A	1/5/2024	1
1/2/2024	B	1/8/2024	1
1/3/2024	C	1/12/2024	1
1/4/2024	D	1/15/2024	0
2/1/2024	E	2/5/2024	0
2/1/2024	F	2/12/2024	0
2/1/2024	G	2/17/2024	1
2/1/2024	H	2/18/2024	1
2/1/2024	I	2/19/2024	1

This table has a date of creation, a productname, a date of when they fall on delincuency and wheter they have fallen on delincency or not. I would like to create a running sum measure in dax that when filtering by date of creation through a date table (for example, January) would allow me to calculate the sum of value between al calendar dates withing default date, that would yield a result looking like this:

Date	Value	Running Sum
1/1/2024	0	0
1/2/2024	0	0
1/3/2024	0	0
1/4/2024	0	0
1/5/2024	1	1
1/6/2024	0	1
1/7/2024	0	1
1/8/2024	1	2
1/9/2024	0	2
1/10/2024	0	2
1/11/2024	1	3
1/12/2024	0	3
1/13/2024	0	3
1/14/2024	0	3
...TODAY	1	4

This way I would have a running total of the clients that have defaulted over the actual period since their deal was created and not just a running sum over the dates they default.

I have already tried differnt methods, but i have only beend able to make it work 

Value Over Time = 
VAR MaxDate = MAX(Delincuency)
RETURN
CALCULATE(
    SUM(Value), 
    FILTER(ALLSELECTED(Date Table), 
    Date Table<=MaxDate))
)

This works under normal circumstances, but doesnt do as i need, which is to have the poper range between when i selecte a creation date and today and then the running total over that.

[v-nuoc-msft · Accepted solution]

Hi @aulla 

Thank you very much PhilipTreacy for your prompt reply.

Is your problem solved? If not, let me add something to PhilipTreacy's answer.

You need to have a table to create the slicer.

DateSlicer = CALENDAR("1/1/2024", "12/31/2024")

Create a slicer.

Create a measure.

RT = 
CALCULATE(
	SUM('DataTable'[Value]),
	FILTER(
		ALL('DateTable'),
		ISONORAFTER('DateTable'[Date], MAX('DateTable'[Date]), DESC)
        &&
        MONTH('DateTable'[Date]) >= SELECTEDVALUE('DateSlicer'[Date].[MonthNo])
	)
)

You also need to create a measure to filter the data in the table.

Measure = 
IF(
    MONTH(SELECTEDVALUE('DateTable'[Date])) >= SELECTEDVALUE('DateSlicer'[Date].[MonthNo]),
    1,
    0
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aulla]

Wonderfull, pretty much thats what i needed. Just want to add an extra filter to the measure so it only works with the selected month: 

RT = 
CALCULATE(
	SUM('DataTable'[Value]),
	FILTER(
		ALLSELECTED('DateTable'),
		ISONORAFTER('DateTable'[Date], MAX('DateTable'[Date]), DESC)
        &&
        MONTH('DateTable'[Date]) >= SELECTEDVALUE('DateSlicer'[Date].[MonthNo])
    )
    ,MONTH('DataTable'[Created])<= SELECTEDVALUE('DateSlicer'[Date].[MonthNo])
	
)

[aulla]

Hi @PhilipTreacy 

Ver interesting function that i didnt know about. Is there any way to make it work by filtering by creation month date?  The idea is to see how the products have evolved over time since their date of creation. For example, if i filter by february or january it should look something like this:

[PhilipTreacy]

Hi @aulla 

Download example PBIX file

I've created a Datetable and created a relationship between it and the Delinquency column.

Try this

RT = 
CALCULATE(
	SUM('DataTable'[Value]),
	FILTER(
		ALLSELECTED('DateTable'[Date]),
		ISONORAFTER('DateTable'[Date], MAX('DateTable'[Date]), DESC)
	)
)

Regards

Phil

[aulla]

Hi Philip,

Quite an interesting function i dindt know about. How can i make it work with a date of creation filter?

For example if i filter by products created in february i sohuld look like this:

Date	Value	Runnig Total
2/1/2024	0	0
2/2/2024	0	0
2/3/2024	0	0
2/4/2024	0	0
2/5/2024	0	0
2/6/2024	0	0
2/7/2024	0	0
2/8/2024	0	0
2/9/2024	0	0
2/10/2024	0	0
2/11/2024	0	0
2/12/2024	1	1
2/13/2024	0	1
2/14/2024	0	1
2/15/2024	0	1
2/16/2024	0	1
2/17/2024	1	2
2/18/2024	1	3
2/19/2024	1	4
…Today	0	4

or if i filter by those created in january:

Date	Value	Runnig Total
1/1/2024	0	0
1/2/2024	0	0
1/3/2024	0	0
1/4/2024	0	0
1/5/2024	1	1
1/6/2024	0	1
1/7/2024	0	1
1/8/2024	1	2
1/9/2024	0	2
1/10/2024	0	2
1/11/2024	0	2
1/12/2024	1	3
1/13/2024	0	3
1/14/2024	0	3
1/15/2024	1	4
1/16/2024	0	4
1/17/2024	0	4
1/18/2024	0	4
1/19/2024	0	4
…Today	0	4

Those tables look quite bad. Leaving a picture for better understanding: