Forum Discussion

hackfifi's avatar
hackfifi
Icon for Helper V rankHelper V
9 years ago
Solved

Flat Line in Cumulative Curve

Hey Guys - Any idea how to get rid of the BLACK FLAT LINE for Actuals? The Actual Curve should stop after TODAY's Date. The data extract is also shown below.

 

The Formula used for the Actual Curve is:

Cumulative Actual = CALCULATE (

    SUM ('Contract Closeout Combined'[A]),

    FILTER (

        ALLSELECTED('Contract Closeout Combined'),

        ('Contract Closeout Combined'[Date] <= max('Contract Closeout Combined'[Date]))

))/sum('Total Progress (Forecast)'[Total Progress (Forecast)])

 

i tried replacing "('Contract Closeout Combined'[Date] <= max('Contract Closeout Combined'[Date]) with Today()"

but that did not work. 

 

Need Help
  • GilbertQ's avatar
    GilbertQ
    9 years ago

    Hi hackfifi

     

    What I would suggest you do is the following:

     

    Create a measure for your A Value with the example below: 

    A Total = IF(SUM('Table2'[A]) = 0,BLANK(),SUM('Table2'[A]))

     The above will always ensure that there is one place to change it, as well as then make it easier for the filter context later.

     

    Then with the above measure you can now create your cumulative curve with the following measure.

     

    Cumulative Actual = 
Var LNBD = LASTNONBLANK('Table2'[Date],[A Total])
RETURN
CALCULATE([A Total],FILTER(ALL('Table2'[Date]), 'Table2'[Date] <= LNBD))
  • GilbertQ's avatar
    GilbertQ
    9 years ago
    Hi there for your Year and Month values are those coming from the 'Progress Curve' table or the date table?

    I think that might be the reason why it is not working as expected.

24 Replies

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    9 years ago

    Hi hackfifi


    This should work below, might need one round bracket

     

    Cumulative Actual =
CALCULATE (
    SUM ( 'Contract Closeout Combined'[A] ),
    FILTER (
        ALLSELECTED ( 'Contract Closeout Combined' ),
        (
            'Contract Closeout Combined'[Date]
                <= LASTNONBLANK (
                    'Contract Closeout Combined'[Date],
                    SUM ( 'Contract Closeout Combined'[A] )
                )
        )
    )
)
    / SUM ( 'Total Progress (Forecast)'[Total Progress (Forecast)] )
    • GilbertQ's avatar
      GilbertQ
      Icon for Super User rankSuper User
      9 years ago

      Hi hackfifi

       

      Apologies Monday morning try this instead

       

      CALCULATE (
    SUM ( 'Contract Closeout Combined'[A] ),
    FILTER (
        'Contract Closeout Combined' ,        
            'Contract Closeout Combined'[Date]
                <= LASTNONBLANK (
                    'Contract Closeout Combined'[Date],
                    SUM ( 'Contract Closeout Combined'[A] )
                )
        )    
)
    / SUM ( 'Total Progress (Forecast)'[Total Progress (Forecast)] )
      • hackfifi's avatar
        hackfifi
        Icon for Helper V rankHelper V
        9 years ago

        Thanks again GilbertQ! - but no luck! :)

        I have attached updated curve and data snippet of that point. Do you think it has anything to do with null/0 values?

        Also you will see anything afte 11th June 2017 is 0, as it is TODAY'S date. (data snippet also attached)