Cumulative count by categories

[francomaestri]

Greetings,

I'm trying to count the occurrence of certain events on some dates, but I can't calculate it correctly, so when I create a chart, the occurrence of a new event (new category) starts from zero. That is, each line on the chart starts from zero the first time it appears.

This is what I have so far: 

(Table as an example)

Date	Category
08-07-2022	A
08-07-2022	B
08-07-2022	A
14-08-2022	A
14-08-2022	C
09-09-2022	C
09-09-2022	A
09-09-2022	B
09-09-2022	B

COUNTING = CALCULATE( COUNTROWS('Table'),FILTER(ALL('Table'), 'Table'[Category] <= MAX('Table'[Category]) && 'Table'[Date] <= MAX('Table'[Date])))

[francomaestri · Accepted solution]

Ok, I solved it.

I just created an index in power query, then I created a new column with rankx:

Count with rankx= RANKX(

    FILTER ( 'Table', 'Table'[Category] = EARLIER ( 'Table'[Category] ) ),

    'Table'[Index], ,ASC )

Then I just put Date in X-axis, the new column in Y-axis and Category in legend.
This way I can see when a new event occurs and how many times is repeated over time an when.

[mangaus1111]

Hi @francomaestri ,

if it were a table is this your expected result? If not, please show what is your expected result.

[francomaestri]

Hi!
I think that the result in a table would be:

Date	Category	Count
08-07-2022	A	1
08-07-2022	B	1
08-07-2022	A	2
14-08-2022	A	3
14-08-2022	C	1
09-09-2022	C	2
09-09-2022	A	4
09-09-2022	B	2
09-09-2022	B	3

If I'm not mistaken, in this way in the graph, for example, the line of category C will start at 1 on 08-14-2022. I could just split each event type (category) into a different column, but the category of events may change in the future, so I wanted to keep the table as it is (also because there are many more columns than shown here).

[FreemanZ]

If there is no misunderstanding, you measure shall be like this:

COUNTING = 

VAR CurrentDate = MAX('Table'[Date])

RETURN

CALCULATE( 

    COUNTROWS('Table'),

    FILTER(

        ALL('Table'), 

        'Table'[Date] <= CurrrentDate

    )

)

 

The [Category] field shall be put in the Legend of the linechart visual, or?

[francomaestri]

I'm having the same problem. When a new event appears it does not start from zero.

[Jayee]

Hi @francomaestri 

Counting =

VAR MaxDate = MAX ( 'Table'[Date] ) 

RETURN

    CALCULATE (

        COUNTROWS('Table'),

        'Table'[Date] <= MaxDate,

        ALL ( Table )

    )

Use this measure in chart and add Category as legend.

If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!

[francomaestri]

Sadly I'm having the same problem. Each new event starts from a higher value, not from zero.