Running total for 'row count'

[BartVanH]

Hi,

I'm looking for a variant of the often mentioned running total calculation.

My data does not contain the numbers to add up in the running total, like most Sales data examples around.

My data IS the number to add up, each row being a ticket at a Service Desk.

I would like to know the running total for tickets created and/or closed, by Date:

Source data
Nr	Creation	Closure
INC0001	1/01/2019	1/05/2019
INC0002	1/01/2019	1/06/2019
INC0003	1/02/2019	1/06/2019
REQ0001	1/01/2019	1/07/2019
REQ0002	1/02/2019	1/07/2019
REQ0003	1/02/2019	1/08/2019

Filtering 'INC*' from 'REQ*' can be done on a visual level, so I don't think that needs to be in the code(?)

So I would like to end up with these results (not necessarily combined in 1 measure/visual/...):

Incidents opened			Incidents closed
01/2019	2		05/2019	1
02/2019	3		06/2019	3
Requests opened			Requests closed
01/2019	1		07/2019	2
02/2019	3		08/2019	3

Thanks for the help!

[v-danhe-msft]

Hi @BartVanH,

Based on my test, you could refer to below steps:

Unpivot your Creation and Closure column in query editor:

Result:

Apply it and create below measures:

Measure = CALCULATE(COUNT(Table1[Value]))

Measure running total in value = 
SUMX(FILTER(ALLSELECTED(Table1[Value]),'Table1'[Value]<=MAX('Table1'[Value])),[Measure])

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[BartVanH]

Hi @v-danhe-msft,

Thanks a lot!

The calculation seems to work perfectly!

Here it is in a line graph:

However, when I select the Date Hierarchy in the field well:

It all reverts back to the individual values once I start drilling down...

I have a feeling I know why it's doing that, but is there any way around?

The first version is not my favorite, it's not the most aesthetic way to display the data.

But if there is no way around, I'll use that.

Thanks again!

Bart

[v-danhe-msft · Accepted solution]

Hi @BartVanH, 

You could refer to modifying your formula as below:

Measure running total in value = 
SUMX(FILTER(ALLEXCEPT(Table1,Table1[Attribute],Table1[Legend],Table1[Nr]),'Table1'[Value]<=MAX('Table1'[Value])),[Measure])

Result:

Regards,

Daniel He

[BartVanH]

Beautiful, thanks!