Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Cumulative number

Hi there,

As a new Power BI User/enthusiast, I apologize for this question that was asked maybe a thousand of times, but I can't mix solutions I find with my very case,

 

I got a list of sites created at a specific date, and I need to make a cumulative count :

 

 

Sites sum = 
    CALCULATE (
        COUNTROWS( 'site listing' ),
        'site listing'[CI STATUS] = "Operational",
        FILTER (
            ALL('site listing'),
            'site listing'[CREATED] <= MAX( 'Site listing'[CREATED] )
        )
    )

 

 

This formula works well on its own, but when I try to use this in a dynamic visual, i'm stuck :

 

You can see in deep blue what I should have, and in orange what I actually have with the measure above; My result in deep blue comes from a workaround I did with a made-up table to count, that's a really heavy and not convenient way to do the job.

 

Can someone tell me where I'm wrong with my formula? Thank you very much in advance,

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Try kike

     

    Sites sum =
    CALCULATE (CALCULATE (
    COUNTROWS( 'site listing' ),
    'site listing'[CI STATUS] = "Operational"),
    FILTER (
    ALL('site listing'),
    'site listing'[CREATED] <= MAX( 'Site listing'[CREATED] )
    )
    )

     

    or

     

    Sites sum =
    CALCULATE (CALCULATE (
    COUNTROWS( 'site listing' ),
    'site listing'[CI STATUS] = "Operational"),
    FILTER (
    ALLSELECTED('site listing'),
    'site listing'[CREATED] <= MAX( 'Site listing'[CREATED] )
    )
    )

3 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous  You need to define the MAX as a variable and shouldn't need the FILTER table expression:  

     

    Sites sum = 
VAR _MaxCreated = MAX( 'Site listing'[CREATED] 
    CALCULATE (
        COUNTROWS( 'site listing' ),
        'site listing'[CI STATUS] = "Operational",
        
            'site listing'[CREATED] <= _MaxCreated
        )
    )

      

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello Ms Kennedy,

       

      Thank you very much for taking time to answer me,

       

      As your code didn't work the way you pasted it, I made some adjustments :

       

      Sites sum = //Cumulative number of sites in records, based on "operational" status and the creation date
VAR _MaxCreated = MAX( 'Site listing'[CREATED])
RETURN {    
    CALCULATE(
        COUNTROWS( 'site listing' ),
            'site listing'[CI STATUS] = "Operational",
            'site listing'[CREATED] <= _MaxCreated
    )
}

      Do you agree with my adjustments ? Or do I altered the behavior ?

       

      Unfortunately, when I try this, I get the exact same result : When I use it over months filters, it stays on the max value.

       

      So maybe, is my problem around the settings of my visual? Filters, fields, ... ? How would you set this up ?

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try kike

     

    Sites sum =
    CALCULATE (CALCULATE (
    COUNTROWS( 'site listing' ),
    'site listing'[CI STATUS] = "Operational"),
    FILTER (
    ALL('site listing'),
    'site listing'[CREATED] <= MAX( 'Site listing'[CREATED] )
    )
    )

     

    or

     

    Sites sum =
    CALCULATE (CALCULATE (
    COUNTROWS( 'site listing' ),
    'site listing'[CI STATUS] = "Operational"),
    FILTER (
    ALLSELECTED('site listing'),
    'site listing'[CREATED] <= MAX( 'Site listing'[CREATED] )
    )
    )