Syntax for Cumulative Count Measure with multiple Criteria

[RichardJ]

Hi,

I have this cumulative measure which works ok:

Cumulative Change Notes =
CALCULATE (
    COUNTA ( Company_Documents[ID] ),
    FILTER (
        ALLSELECTED ( Company_Documents ),
        Company_Documents[DOC_CREATION_DATE]
            <= MAX ( Company_Documents[DOC_CREATION_DATE] )
    )
)

However i'd like to limit the result by adding an additional criteria to the filter (starts &&)

Cumulative Change Notes =
CALCULATE (
    COUNTA ( Company_Documents[ID] ),
    FILTER (
        ALLSELECTED ( Company_Documents ),
        Company_Documents[DOC_CREATION_DATE]
            <= MAX ( Company_Documents[DOC_CREATION_DATE] )
            && Company_Documents[Document Class] = "Change Note"
    )
)

I must have the incorrect syntax as there are no records returned despite records being available which match the criteria.

Can anyone give me a pointer on where i've gone wrong when adding the additional filter criteria?

Thanks,
Richard

[Mariusz · Accepted solution]

Hi @RichardJ 

Try this.

Cumulative Change Notes =
VAR __maxCreationDate = MAX ( Company_Documents[DOC_CREATION_DATE] )
RETURN 
CALCULATE (
    COUNTA ( Company_Documents[ID] ),
    ALLSELECTED ( Company_Documents ),
    Company_Documents[DOC_CREATION_DATE] <= __maxCreationDate,
    Company_Documents[Document Class] = "Change Note"
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Mariusz · Accepted solution]

Hi @RichardJ 

Try this.

Cumulative Change Notes =
VAR __maxCreationDate = MAX ( Company_Documents[DOC_CREATION_DATE] )
RETURN 
CALCULATE (
    COUNTA ( Company_Documents[ID] ),
    ALLSELECTED ( Company_Documents ),
    Company_Documents[DOC_CREATION_DATE] <= __maxCreationDate,
    Company_Documents[Document Class] = "Change Note"
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[RichardJ]

@Mariusz Many Thanks for the idea and the quick reply.

This works perfectly.

Initially I didn't think it had worked as I wasn't giving it enough time to calculate (circa 5 seconds) the response.

Thanks again,

Richard

[Mariusz · Accepted solution]

Hi @RichardJ

Try this instead, if it does not work consider creating a data sample so we can test and apply formulas.

Cumulative Change Notes =
VAR __maxCreationDate = MAX ( Company_Documents[DOC_CREATION_DATE] )
RETURN
CALCULATE (
COUNTROWS( Company_Documents ),
FILTER(
ALLSELECTED ( Company_Documents[DOC_CREATION_DATE], Company_Documents[Document Class] ),
AND(
Company_Documents[DOC_CREATION_DATE] <= __maxCreationDate,
Company_Documents[Document Class] = "Change Note"
)
)​

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[RichardJ]

@Mariusz - The linked file shows the formulas you provided working. perfectly.

I had to roll back my actual file (not this extract) to get it working properly as it had become corrupted in some way.

Hopefully the attached working example will help someone in future.

Thanks again @Mariusz - getting a solution for this made my day.

Cheers,

Richard

Cumulative Total, then broken down cumulative sub sections