How can I do cumulative count on a Year column?

[Anonymous]

I would like to do a count first as the "Year" column has repeating years and then do a cumulative count, is it possible?

[FreemanZ]

hi @Anonymous 

can you elaborate your expection with a table?

[Anonymous]

Hi Freemanz,

Let's say I have a table with the below data,

Column "Year" - has repeating values. With the table below, I would like to do a cumulative count on the 'Year" column.

I am trying to do a ribbon chart that will show me e.g from Year 2017 to Year 2022 the total number of  active titles, so the count should be cummulative.

Index	Title	Category	Status	Year
1	ABC	A	Active	2015
2	DEF	A	Active	2017
3	GHI	B	Active	2015
4	JFG	B	Active	2016
5	TIR	C	Active	2019
6	DEC	C	Active	2016

Somthing like below

Year
2017	1	1
2018	2	3
2019	3	6
2020	4	10

[FreemanZ]

hi @Anonymous 

supposing this is your dataset:

Year
2017
2018
2018
2019
2019
2019
2020
2020
2020
2020

try to plot a table visual with the [Date] column and a measure like this:

CountRT = 
COUNTROWS(
    FILTER(
        ALL(TableName),
        TableName[Year]<=MAX(TableName[Year])
    )
)

it worked like this:

[Anonymous]

Hi FreemanZ,

I have rows in the Year column that is blank which it is counting 128 blank as the starting number, possible that it doesn't pick up the blank rows ?

[FreemanZ]

hi @Anonymous 

you may choose not to show the blank rows in the filter pane. 

p.s. consider @ someone, if you expect to continue a discussion. 

[Anonymous]

Hi Freemanz,

It worked! But would it be possible to not filter by all? I would like it to filter by a case status column and only active status?

Thank you.

[FreemanZ]

HI @Anonymous 

then change the code to :

CountRT = 
COUNTROWS(
    FILTER(
        ALL(TableName[Year]),
        TableName[Year]<=MAX(TableName[Year])
    )
)