Forum Discussion

RichardJ's avatar
RichardJ
Responsive Resident
6 years ago
Solved

Syntax for Cumulative Count Measure with multiple Criteria

Hi, I have this cumulative measure which works ok:       Cumulative Change Notes = CALCULATE ( COUNTA ( Company_Documents[ID] ), FILTER ( ALLSELECTED ( Company_Documents ), ...
Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    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's avatar
    Mariusz
    6 years ago

    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
Mariusz's avatar
Mariusz
Community Champion
6 years ago

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's avatar
RichardJ
Responsive Resident
6 years ago

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