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.

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

    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'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